Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Mail Merge to E-mail with Attachments
I have been using a macro that I got from the word.mvps.org website to Mail
Merge to E-mail with Attachments and it's working great. I am using Word 2002 and I would like to set the email "From" field to my boss's name. I need to send out several hundred email messages and I would like to send them on his behalf from my computer. My boss has given me delegation rights to send on his behalf but no matter what I do when I run the macro the email messages are from me. Can anyone help with the vba code to add this to the macro script? I think that's the only way that I'll be able to get this to work. Thank you... Following is the macro script: Mail Merge to E-mail with Attachments Article contributed by Doug Robbins http://word.mvps.org/faqs/mailmerge/...ttachments.htm Sub emailmergewithattachments() Dim Source As Document, Maillist As Document Dim Datarange As Range Dim Counter As Integer, i As Integer Dim bStarted As Boolean Dim oOutlookApp As Outlook.Application Dim oItem As Outlook.MailItem Dim mysubject As String, message As String, title As String Set Source = ActiveDocument ' Check if Outlook is running. If it is not, start Outlook On Error Resume Next Set oOutlookApp = GetObject(, "Outlook.Application") If Err 0 Then Set oOutlookApp = CreateObject("Outlook.Application") bStarted = True End If ' Open the catalog mailmerge document With Dialogs(wdDialogFileOpen) ..Show End With Set Maillist = ActiveDocument ' Show an input box asking the user for the subject to be inserted into the email messages message = "Enter the subject to be used for each email message." ' Set prompt. title = " Email Subject Input" ' Set title. ' Display message, title mysubject = InputBox(message, title) ' Iterate through the rows of the catalog mailmerge document, extracting the information ' to be included in each email. Counter = 1 While Counter = Maillist.Tables(1).Rows.Count Source.Sections.First.Range.Cut Documents.Add Selection.Paste Set oItem = oOutlookApp.CreateItem(olMailItem) With oItem ..Subject = mysubject ..Body = ActiveDocument.Content Set Datarange = Maillist.Tables(1).Cell(Counter, 1).Range Datarange.End = Datarange.End - 1 ..To = Datarange For i = 2 To Maillist.Tables(1).Columns.Count Set Datarange = Maillist.Tables(1).Cell(Counter, i).Range Datarange.End = Datarange.End - 1 ..Attachments.Add Trim(Datarange.Text), olByValue, 1 Next i ..Send End With Set oItem = Nothing ActiveDocument.Close wdDoNotSaveChanges Counter = Counter + 1 Wend ' Close Outlook if it was started by this macro. If bStarted Then oOutlookApp.Quit End If 'Clean up Set oOutlookApp = Nothing Source.Close wdDoNotSaveChanges Maillist.Close wdDoNotSaveChanges End Sub |
#2
|
|||
|
|||
You must copy his e-mail account to your PC and temporarily set it as
default while you complete the merge. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Cay wrote: I have been using a macro that I got from the word.mvps.org website to Mail Merge to E-mail with Attachments and it's working great. I am using Word 2002 and I would like to set the email "From" field to my boss's name. I need to send out several hundred email messages and I would like to send them on his behalf from my computer. My boss has given me delegation rights to send on his behalf but no matter what I do when I run the macro the email messages are from me. Can anyone help with the vba code to add this to the macro script? I think that's the only way that I'll be able to get this to work. Thank you... Following is the macro script: Mail Merge to E-mail with Attachments Article contributed by Doug Robbins http://word.mvps.org/faqs/mailmerge/...ttachments.htm Sub emailmergewithattachments() Dim Source As Document, Maillist As Document Dim Datarange As Range Dim Counter As Integer, i As Integer Dim bStarted As Boolean Dim oOutlookApp As Outlook.Application Dim oItem As Outlook.MailItem Dim mysubject As String, message As String, title As String Set Source = ActiveDocument ' Check if Outlook is running. If it is not, start Outlook On Error Resume Next Set oOutlookApp = GetObject(, "Outlook.Application") If Err 0 Then Set oOutlookApp = CreateObject("Outlook.Application") bStarted = True End If ' Open the catalog mailmerge document With Dialogs(wdDialogFileOpen) .Show End With Set Maillist = ActiveDocument ' Show an input box asking the user for the subject to be inserted into the email messages message = "Enter the subject to be used for each email message." ' Set prompt. title = " Email Subject Input" ' Set title. ' Display message, title mysubject = InputBox(message, title) ' Iterate through the rows of the catalog mailmerge document, extracting the information ' to be included in each email. Counter = 1 While Counter = Maillist.Tables(1).Rows.Count Source.Sections.First.Range.Cut Documents.Add Selection.Paste Set oItem = oOutlookApp.CreateItem(olMailItem) With oItem .Subject = mysubject .Body = ActiveDocument.Content Set Datarange = Maillist.Tables(1).Cell(Counter, 1).Range Datarange.End = Datarange.End - 1 .To = Datarange For i = 2 To Maillist.Tables(1).Columns.Count Set Datarange = Maillist.Tables(1).Cell(Counter, i).Range Datarange.End = Datarange.End - 1 .Attachments.Add Trim(Datarange.Text), olByValue, 1 Next i .Send End With Set oItem = Nothing ActiveDocument.Close wdDoNotSaveChanges Counter = Counter + 1 Wend ' Close Outlook if it was started by this macro. If bStarted Then oOutlookApp.Quit End If 'Clean up Set oOutlookApp = Nothing Source.Close wdDoNotSaveChanges Maillist.Close wdDoNotSaveChanges End Sub |
#3
|
|||
|
|||
Thanks Graham, but I have a "lightly managed" machine and I'm only allowed
one e-mail account. I did find the code to fix problem under Help in Outlook and it works! I used the SentOnBehalfOfName Property. I learn a little more everytime I use this macro. Thanks again. "Graham Mayor" wrote: You must copy his e-mail account to your PC and temporarily set it as default while you complete the merge. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Cay wrote: I have been using a macro that I got from the word.mvps.org website to Mail Merge to E-mail with Attachments and it's working great. I am using Word 2002 and I would like to set the email "From" field to my boss's name. I need to send out several hundred email messages and I would like to send them on his behalf from my computer. My boss has given me delegation rights to send on his behalf but no matter what I do when I run the macro the email messages are from me. Can anyone help with the vba code to add this to the macro script? I think that's the only way that I'll be able to get this to work. Thank you... Following is the macro script: Mail Merge to E-mail with Attachments Article contributed by Doug Robbins http://word.mvps.org/faqs/mailmerge/...ttachments.htm Sub emailmergewithattachments() Dim Source As Document, Maillist As Document Dim Datarange As Range Dim Counter As Integer, i As Integer Dim bStarted As Boolean Dim oOutlookApp As Outlook.Application Dim oItem As Outlook.MailItem Dim mysubject As String, message As String, title As String Set Source = ActiveDocument ' Check if Outlook is running. If it is not, start Outlook On Error Resume Next Set oOutlookApp = GetObject(, "Outlook.Application") If Err 0 Then Set oOutlookApp = CreateObject("Outlook.Application") bStarted = True End If ' Open the catalog mailmerge document With Dialogs(wdDialogFileOpen) .Show End With Set Maillist = ActiveDocument ' Show an input box asking the user for the subject to be inserted into the email messages message = "Enter the subject to be used for each email message." ' Set prompt. title = " Email Subject Input" ' Set title. ' Display message, title mysubject = InputBox(message, title) ' Iterate through the rows of the catalog mailmerge document, extracting the information ' to be included in each email. Counter = 1 While Counter = Maillist.Tables(1).Rows.Count Source.Sections.First.Range.Cut Documents.Add Selection.Paste Set oItem = oOutlookApp.CreateItem(olMailItem) With oItem .Subject = mysubject .Body = ActiveDocument.Content Set Datarange = Maillist.Tables(1).Cell(Counter, 1).Range Datarange.End = Datarange.End - 1 .To = Datarange For i = 2 To Maillist.Tables(1).Columns.Count Set Datarange = Maillist.Tables(1).Cell(Counter, i).Range Datarange.End = Datarange.End - 1 .Attachments.Add Trim(Datarange.Text), olByValue, 1 Next i .Send End With Set oItem = Nothing ActiveDocument.Close wdDoNotSaveChanges Counter = Counter + 1 Wend ' Close Outlook if it was started by this macro. If bStarted Then oOutlookApp.Quit End If 'Clean up Set oOutlookApp = Nothing Source.Close wdDoNotSaveChanges Maillist.Close wdDoNotSaveChanges End Sub |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mail Merge - Parameter prompts twice... | Mailmerge | |||
Mail Merge with Attachments | Mailmerge | |||
mail merge with attachments | Mailmerge | |||
How do you add attachments in Word for mail merge emails? | Tables | |||
Mail Merge Issue With Office 97 - Excel Data Source | Mailmerge |