Change Template Recipient Automatically - Outlook 2007

Status
Not open for further replies.

walu22

Member
Outlook version
Outlook 2007
Email Account
Hello,

I have an Outlook distribution list of ~50 clients, each of whom needs to be emailed a personal Excel report once a week. I want to produce a macro that would loop through the distribution list, pull the individual email addresses, and apply each address to its own template. The template has a subject line and message (that's the same for everyone) while the new attachment is added manually. In other words, after running the macro, I’d like to see 50 separate drafts, one for each client in the list, with a different email address in each “to” field. Can you fine folks help me out? I have no clue how to write VBA code myself, I just copy, paste, and pray, using stuff I find online. If a macro wouldn't be the best solution, is there another way I could do this?



Cheers,

walu22

P.S. Through searching around online, including stuff posted on this site, I've found the following:

Public Sub InsertSubject()

Dim olkMsg As Outlook.MailItem

Set olkMsg = Application.CreateItem(olMailItem)

olkMsg.Subject = "Client Statement"

olkMsg.Display

olkMsg.To = "*** Email address is removed for privacy ***

Set olkMsg = Nothing

End Sub

Could some sort of loop be added to olkMsg.To in this bad boy?

 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange

walu22

Member
Outlook version
Outlook 2007
Email Account
Will the excel file names change week to week? if not, you can automate everything.

You sample macro will create 1 message - you need to loop through the dl to get each address - see http://www.slipstick.com/developer/mail-merge-members-contact-group/
The excel file name does change, but I guess it doesn't have to. We currently do it so that each file name includes the date for the week end (i.e. the report is sent out on Monday, contains data from the previous week, and is dated as per this example: Client Statement - Week End October 18, 2013). How would I go about automating everything?
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
If the name is consistent and something VBA can be made to "figure out", it shouldn't be a problem - dates and date calculations are something vba can handle. Is the clients name in the filename or folder path? You need a way to identify and link the person, the email address, and the file. If you generate the files one at a time, you could generate and send in one step. (Yeah, lots of options. :))
 

walu22

Member
Outlook version
Outlook 2007
Email Account
Will the excel file names change week to week? if not, you can automate everything.

You sample macro will create 1 message - you need to loop through the dl to get each address - see http://www.slipstick.com/developer/mail-merge-members-contact-group/
I just followed your instructions, Diane, and the code worked perfectly. This is exactly what I'm looking for! If you're ever in Winnipeg, lunch's on me. :)

I am still interested in automating the entire process, but this is a fantastic start.

Thank you,

walu22
 

walu22

Member
Outlook version
Outlook 2007
Email Account
If the name is consistent and something VBA can be made to "figure out", it shouldn't be a problem - dates and date calculations are something vba can handle. Is the clients name in the filename or folder path? You need a way to identify and link the person, the email address, and the file. If you generate the files one at a time, you could generate and send in one step. (Yeah, lots of options.
)
Hey Diane,

Apologies for replying so late! Work's gotten crazy, but the mail merge is drawing oohs and ahs from my coworkers! Would saving the files in the same order as the names in distribution list work? In other words, a way to ask VBA to pull the first name in the list and the first file in a folder, which will be matched due to the order in which I save the files?

As per your request, here's the actual format of the files:

The main reports are in the following location:

Individual Reports -> Year (i.e 2013) -> Week (i.e 42. Oct 21-25) -> Statements -> File Name (i.e. LastName_Earned_2013_10_25)

Meanwhile, the quarterly reports are in the following location:

Individual Reports -> Year (i.e 2013) -> Week (i.e 42. Oct 21-25) -> Deferred -> Statements -> File Name (i.e LastName_Deferred_2013_10_18)

The last name in the file name matches the first name listed in the distribution list (i.e. Doe, John in my "to field" verses Doe_Earned_2013_10_25)

With regards to your final point, the reports are all generated first, saved on our shared drive, and reviewed, before the client emails are sent out. So generating and sending in one step won't really work for me.

Happy Halloween,

Colin
 
Status
Not open for further replies.
Thread starter Similar threads Forum Replies Date
LarryS change day template as cpao does not see .catx files Outlook VBA and Custom Forms 4
Y How to change Outlook template from powerpoint to pdf Using Outlook 1
diver864 vba for a rule to automatically accept meeting requests with 'vacation' in subject, change to all-day event, change to free, don't send reply Outlook VBA and Custom Forms 1
B Change Font and Font size using VBA Outlook VBA and Custom Forms 9
D Change senders title Using Outlook 1
W Recurrence: delete older occurrences / change earliest start time Outlook VBA and Custom Forms 0
E Change sending account depending on Subjectline Outlook VBA and Custom Forms 0
J Outlook 2013 Change color of text in data fields of contacts in Outlook 2013? Using Outlook 3
B Change row background color of selected item Using Outlook 1
PGSystemTester VBA To Change AppointmentItem.BusyStatus From MeetingItem Before Send Using Outlook 0
B Change from Address Outlook VBA and Custom Forms 0
X If you change expiration date of repeated task it dupplicates Using Outlook 1
E How to display "Change Folder" in Change Default Email Delivery Location in Exchange Outlook 2016 Using Outlook 1
B See "Change View" Drop Down as a List? Using Outlook 1
V Change start time based on message duration Outlook VBA and Custom Forms 2
R Folder pane width change Using Outlook 59
S Change VBA script to send HTML email instead of text Outlook VBA and Custom Forms 3
S Outlook 2010 unable to change default font Using Outlook 7
P How can I change my calendar view back Using Outlook 3
A Edit subject - and change conversationTopic - using VBA and redemption Outlook VBA and Custom Forms 2
T Change the selected Message in the Outlook window Outlook VBA and Custom Forms 2
geofferyh How to change the Attachment File Name? Outlook VBA and Custom Forms 1
W Appointment occurrences change the location property Using Outlook 0
T Scheduled footer change Using Outlook 2
B Change font of reminder of an email header Outlook VBA and Custom Forms 3
CWM030 Name Change? Exchange Server Administration 9
V not able to change name in customize Ribbon Outlook VBA and Custom Forms 1
JoeG Appointment Delete/Change Recurrence Outlook VBA and Custom Forms 0
C Change Subject Line in Selected Emails Outlook VBA and Custom Forms 1
V Change default default save location to Quick Access Using Outlook 1
C Change default "Save Sent Item To" folder Outlook VBA and Custom Forms 9
S Example VBA Macro - To Conditionally Change the From Account and Add a BCC Address on Emails Outlook VBA and Custom Forms 11
B Do not change the From address in Outlook Templates Using Outlook 0
M Sudden change in From field - now very short Using Outlook 4
M cannot change delivery folders with IMAP accounts Using Outlook 0
S Email Format With Embedded Images and Tables Change Using Outlook 2
O On click,I want to change subject line of selected mail and then reply to particular email and move Using Outlook 3
P Change PC User Directory & iPhone Sync Using Outlook 2
A Change order of actions in one (!) rule Outlook VBA and Custom Forms 2
I change subject and forward without FW: Outlook VBA and Custom Forms 4
Rupert Dragwater Can't change font size Using Outlook 5
M VBA to change flag status in outlook contact item Outlook VBA and Custom Forms 3
J Auto Forward - Include Attachment and change Subject depending on original sender Outlook VBA and Custom Forms 3
tjd189 outlook 2016 name change Using Outlook 1
E Button to change subject Outlook VBA and Custom Forms 1
K Paste from Windows Clipboard then change font without losing formatting Outlook VBA and Custom Forms 1
D Office365 Conference Room name change? Using Outlook 5
J Change POP account to Forwarder - What happens in Outlook? Using Outlook 2
H Change Default Email Account Using VBA Outlook VBA and Custom Forms 5
C Change default colors for conditional formatting Using Outlook 2
Similar threads


















































Top