By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,490 Members | 1,789 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

Mail merge to E-mail with attachments using VBA [Outlook 2003]

P: 1
Hello everyone,

Thank you for reading! I am completely new to this so be gentle :)

I am trying to mail merge to e-mail, including attachments with each message. I found this great article by Doug Robbins: http://word.mvps.org/faqs/mailmerge/...ttachments.htm

This article solved my problem, but unfortunately I found out that running the macro strips off all formatting as well as removing the images I had in my original document (I started the mail merge from Word).

Is there any way to alter the code below to send the message as it is (i.e. with formatting and pictures)?

Thank you very much for your help,

Sarah

PS: I am using Windows XP with Microsoft Office 2003.

Expand|Select|Wrap|Line Numbers
  1. Sub emailmergewithattachments()
  2.  
  3. Dim Source As Document, Maillist As Document, TempDoc As Document
  4. Dim Datarange As Range
  5. Dim i As Long, j As Long
  6. Dim bStarted As Boolean
  7. Dim oOutlookApp As Outlook.Application
  8. Dim oItem As Outlook.MailItem
  9. Dim mysubject As String, message As String, title As String
  10.  
  11. Set Source = ActiveDocument
  12.  
  13. ' Check if Outlook is running.  If it is not, start Outlook
  14. On Error Resume Next
  15. Set oOutlookApp = GetObject(, "Outlook.Application")
  16. If Err <> 0 Then
  17.     Set oOutlookApp = CreateObject("Outlook.Application")
  18.     bStarted = True
  19. End If
  20.  
  21. ' Open the catalog mailmerge document
  22. With Dialogs(wdDialogFileOpen)
  23.     .Show
  24. End With
  25. Set Maillist = ActiveDocument
  26.  
  27. ' Show an input box asking the user for the subject to be inserted into the email messages
  28. message = "Enter the subject to be used for each email message."    ' Set prompt.
  29. title = " Email Subject Input"    ' Set title.
  30. ' Display message, title
  31. mysubject = InputBox(message, title)
  32.  
  33. ' Iterate through the Sections of the Source document and the rows of the catalog mailmerge document,
  34. ' extracting the information to be included in each email.
  35. For j = 1 To Source.Sections.Count - 1
  36.     Set oItem = oOutlookApp.CreateItem(olMailItem)
  37.     With oItem
  38.         .Subject = mysubject
  39.         .Body = Source.Sections(j).Range.Text
  40.         Set Datarange = Maillist.Tables(1).Cell(j, 1).Range
  41.         Datarange.End = Datarange.End - 1
  42.         .To = Datarange
  43.         For i = 2 To Maillist.Tables(1).Columns.Count
  44.             Set Datarange = Maillist.Tables(1).Cell(j, i).Range
  45.             Datarange.End = Datarange.End - 1
  46.             .Attachments.Add Trim(Datarange.Text), olByValue, 1
  47.         Next i
  48.         .Send
  49.     End With
  50.     Set oItem = Nothing
  51. Next j
  52. Maillist.Close wdDoNotSaveChanges
  53.  
  54. '  Close Outlook if it was started by this macro.
  55. If bStarted Then
  56.     oOutlookApp.Quit
  57. End If
  58.  
  59. MsgBox Source.Sections.Count - 1 & " messages have been sent."
  60.  
  61. 'Clean up
  62. Set oOutlookApp = Nothing
  63.  
  64. End Sub
  65.  
Jan 18 '12 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,709
Sarah, correct me if I'm wrong but doesn't this code try to include the document text inside the email, rather than adding it as an attachment as you state is the requirement?
Jan 19 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.