Combine Mail Merge to Distribution List with Auto Attachments - Outlook 2007

Not open for further replies.


Outlook version
Email Account

I'm trying to combine the following two pieces of code, both of which were found online, into a macro that will:

1. Loop through a distribution list

2. Create a new email for each listed email address

3. Attach a PDF to each email based on the name in the "to field"

4. Save the completed email in the Drafts Folder for review

I've been fiddling around for a couple of days and can't get it to work at all. I employ the copy, paste, and pray programming model, which may be part of my problem. I thought I'd post it here for you fine folks to look at.

Things to note:

The documents are named like so: Doe, John - Weekly Statement.

The "to field" is formatted as such:

Alternatively, the files are (pretty much) saved in the same order as the names appear in the distribution list. I imagine that this could be used in a loop (i.e. tell Outlook to grab the first file in the folder and attach to the email made from the first name in the list).

Here's the mail merge code:


Sub Merge_Earned_to_Group() 
Dim o_list As Object 
Dim objMsg As MailItem 
Set o_list = GetCurrentItem() 
For i = 1 To o_list.MemberCount 
Set objMsg = Application.CreateItem(olMailItem) 
With objMsg
 .To = o_list.GetMember(i).Address
 'Cycle Date Must Be Changed Every Cycle
 .Subject = "Earned Income for the period October 16-31 2013"
 .Body = "The following are details of your current income payable for the above period." & vbNewLine & _
 "Your current income can be found on the bottom far right corner of your attached document." & vbNewLine
 .Close olPromptForSave 
End With 
Set objMsg = Nothing
End Sub

 Function GetCurrentItem() As Object 
Dim objApp As Outlook.Application 
Set objApp = Application 
On Error Resume Next 
Select Case TypeName(objApp.ActiveWindow) 
Case "Explorer" 
Set GetCurrentItem = objApp.ActiveExplorer.Selection.Item(1) 
Case "Inspector" 
Set GetCurrentItem = objApp.ActiveInspector.CurrentItem 
End Select 
Set objApp = Nothing 
End Function

Here's the automatic attachment code:


Sub SendFilesinFolder() 
Dim sFName As String   
sFName = Dir("C:\Users\walu22\Individual Statements\")Do While Len(sFName) > 0 
Call SendasAttachment(sFName) 
sFName = Dir 
End Sub 
Function SendasAttachment(fName As String) 
Dim olApp As Outlook.Application 
Dim olMsg As Outlook.MailItem 
Dim olAtt As Outlook.Attachments 
Dim strName As String 
strName = InputBox("Enter first 4 characters of filename")
Do While Len(fName) > 0
 If Left(fName, 4) = strName Then
   olAtt.Add fldName & fName
   sAttName = fName & "<br />" & sAttName
  End If
  fName = Dir 
Set olApp = Outlook.Application 
Set olMsg = olApp.CreateItem(0) ' email 
Set olAtt = olMsg.Attachments 
' attach file 
olAtt.Add ("C:\Users\walu22\Individual Statements" & fName) 
With olMsg 
> Subject = "Here's that file you wanted" 
> To = "[EMAIL=""][/EMAIL]" 
> HTMLBody = "Hi " & olMsg.To & "," & vbCrLf & "Attached is " & fName & " you requested." 
> Send 
End With 
End Function

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Re: Combine Mail Merge to Distribution List with Auto Attachments - Outlook 20

This is the problem:

The documents are named like so: Doe, John - Weekly Statement.
The "to field" is formatted as such:

if Doe, john is resolvable to your contacts, you can strip the rest of the file name off and let outlook resolve it. My only concern would be if you had two john doe's. In that case, a file named "Doe, John - clientcompany - Weekly Statement" would be better, because you could check the address for the domain. You could also popup a dialog displaying the resolved address, but that gets old quick
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
J Combine Mail Rules Exchange Server Administration 6
D How To Combine Share Task Folders in just one Folder Using Outlook 0
glnz O365 - How to combine the Inboxes for four email accounts into a single Inbox Using Outlook 7
N Macro for attachment saved and combine Outlook VBA and Custom Forms 1
Diane Poremsky Combine and Print Multiple Outlook Calendars Using Outlook 0
s7evie combine 24 meeting room calendars in to 1 single list Using Outlook 2
F Combine Outlook Task Folders into one View (Outlook 2013) Using Outlook 3
D ISOmacro to extract active mail senders name and email, CC, Subject line, and filename of attachments and import them into premade excel spread sheet Outlook VBA and Custom Forms 2
witzker Outlook 2019 Macro to answer a mail with attachments Outlook VBA and Custom Forms 2
D Outlook 2003 Mail Fails Using Outlook 1
Cathy Rhone Mail merge error message Using Outlook 2
R Sent emails show iCloud mail account not the alias Using Outlook 2
D Advanced e-Mail search on from/to contact group only searches for first 20 contacts in group Using Outlook 0
P Print attachments automatically and move the mail to an existing folder called "Ted" Outlook VBA and Custom Forms 4
P Importing other e-mail accounts into Outlook Using Outlook 1
lcarpay Stay in the mail folder pane after ctrl-1 Using Outlook 1
O Exchange Sync period only (e.g. last years mail) Using Outlook 0
F Excel VBA to move mails for outlook 365 on secondary mail account Outlook VBA and Custom Forms 1
M Convertor for Outlook Express Mail Store (.dbx) to Outlook Mail Store (.pst) Using Outlook 0
T vba extract data from msg file as attachment file of mail message Outlook VBA and Custom Forms 1
J E-mail held in Outbox while Minimized Using Outlook 3
G Forward email body to other mail list directly from Exchange server Exchange Server Administration 1
T Outlook creates a copie of every mail I send Using accounts in Outlook 4
N Please advise code received new mail Using Outlook 0
M Outlook 2010 How could I globally redesign an outlook template form/region/inspector template used to display mail lists or an individual mails? Outlook VBA and Custom Forms 0
A How to stop user form from disapearing once mail window is closed? Outlook VBA and Custom Forms 0
M Outlook, send to > mail recipient - results in plain text email Using Outlook 1
R How to Sync *all* Google Workspace Mail Folders with Outlook 2019 (MS365) Using Outlook 3
S Outlook VBA How to adapt this code for using in a different Mail Inbox Outlook VBA and Custom Forms 0
E Having some trouble with a run-a-script rule (moving mail based on file type) Outlook VBA and Custom Forms 5
S Outlook email to configure setup for each mail Outlook VBA and Custom Forms 1
L Correct E-Mail Pulling Wrong Mail Using Outlook 5
S Outlook mail adressing stops after first match in GAL Using Outlook 0
P Outlook 2013 All imported Mail Rules in error when imported into new profile Using Outlook 5
S Outlook Macro to send auto acknowledge mail only to new mails received to a specific shared inbox Outlook VBA and Custom Forms 0
S Outlook Macro to move reply mail based on the key word in the subjectline Outlook VBA and Custom Forms 0
MrMayor SENT mail not reloading to top?? Using Outlook 4
Terry Sullivan Sender Field Displays My E-Mail Address, Not My Name Using Outlook 1
ThinkToday Calculate reply time of outlook mail Using Outlook 1
B Outlook 2010 Opening Mail Attachment Using Outlook 2
e_a_g_l_e_p_i Is it possible it set the fonts used to read incoming mail Using Outlook 25
D Mail in Sent Items Gone Using Outlook 12
M Managing Gmail "All Mail" sync issues with IMAP Using Outlook 1
GregS Outlook 2016 Sent Mail absent from Sent Mail or Sent Items Folders Using Outlook 4
GregS Why is Outlook 2016 mail in Using Outlook 1
S Outlook not receiving mail Using Outlook 1
Commodore Outlook (2013) issues with mail Using Outlook 0
GregS Outlook 2016 iPhone won't download Outlook 2016 Mail. Using Outlook 0
A Arthur needs help with 2007 Outlook e-mail Using accounts in Outlook 3
oliv- How to distinguish whether mail is incoming or outgoing? Outlook VBA and Custom Forms 2

Similar threads