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
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
O 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
P Limited Support for 3rd Party Mail in new Outlook? Using Outlook 1
T Exchange Server - Mail Delay Stamping Exchange Server Administration 0
L Error when exporting Sent Mail to Excel Outlook VBA and Custom Forms 6
X Run macro automatically when a mail appears in the sent folder Using Outlook 5
K How can I delete an e-mail from Outlook Using Outlook 1
L Help: set flag for sent mail to check if received an answer Outlook VBA and Custom Forms 2
A Macro Mail Alert Using Outlook 4
e_a_g_l_e_p_i MY Outlook 2021 changed the format of the shortcuts for mail, calendar etc. Using Outlook 10
Z Outlook 365 Automatically assign categories to incoming mail in a shared folder Round Robin Outlook VBA and Custom Forms 1
W Outlook 365 I am getting the "Either there is no default mail client" error when I try to send an email on excel Office 365 Using Outlook 1
D Gmail mail is being delivered to a different email inbox in Outlook App 2021 Using Outlook 2
P What is your strategy for dealing with SPAM and Junk Mail? Using Outlook 1
C Code to move mail with certain attachment name? Does Not work Outlook VBA and Custom Forms 3
T 1:1 Datatransfer from incoming mail body to customs form body Outlook VBA and Custom Forms 0
O Mail rule issue Using Outlook 3
A manual rule sends mail to wrong folder Using Outlook 5
Aussie Outlook 365 Rule runs manually but returns the error code "an unexpected error has occurred" when incoming mail arrives Using Outlook 1
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 1
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

Similar threads