Announcement

Collapse
No announcement yet.

Trying to run an Automated Mail Merge from Excel Via Word

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Trying to run an Automated Mail Merge from Excel Via Word



    Hi There,

    The database is full of customer bookings and the email side is for sending automated emails regarding There Gift aid donation.

    Im trying to run an Automated mail merge from excel via word. Basically im opening an a spreadsheet which has the same information as the Mail merge data source, its a refresh-able Query from microsoft query . I want to be able to click a button that opens up Word and produces an email mail merge and sends it to my outbox. So the emails can be viewed before the final send.
    Im using Office 2013,

    • Once i have this stage working i want to eventually move on to having a field on the main spread sheet automatically fill in saying Sent with a Time Stamp.
    • I want to send either email or print a letter depending on what Address/Email information they have.
    • There may also be multiple bookings from the same Customer e.g Restaurant and house booking, which will show separately, and i want to some how have a condition that it puts all the customers Bookings onto one mail merge letter, instead of sending 3 separate letters to one customer.


    i'm very keen on getting the 1st stage working, but any advice would be greatly appreciated.

    this is my code so far;

    Code:
     
    
    Sub OpenWord()
    'Setting up Word Application Dim wdApp as word.application
    On Error Resume Next
    
    
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    
    
    
    
    Set wdApp = GetObject(, "Word.application")
    If wdApp Is Nothing Then
    Set wdApp = GetObject("O:\Accounts\Gamma\GiftAid\GiftAidLettersTest.docx", "word.application")
    End If
    
    
    On Error GoTo 0
    
    
    With wdApp
    
    
    Set wdDoc = .Documents.Open(Filename:="O:\Accounts\Gamma\GiftAid\GiftAidLettersTest.docx")
    
    
    wdDoc.Application.Visible = True
    
    
    wdDoc.MailMerge.OpenDataSource Name:="O:\Accounts\Gamma\GiftAid\GiftAidTestingCSV.csv" _
    , ConfirmConversations:=False, _
    ReadOnly:=False, LinkToSource:=False, AddToRecentFiles:=False, _
    PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
    WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
    Connection:="", SQLStatement:="", SQLStatement1:=""
    
    
    ' Run the Merge
    
    
    With ActiveDocument.MailMerge
            .Destination = wdSendToEmail
            .SuppressBlankLines = True
            .MailSubject = "Test"
            .MailFormat = wdMailFormatHTML
            .MailAddressFieldName = ActiveDocument.MailMerge.DataSource.DataFields("email").Value
            With .DataSource
                .FirstRecord = wdDefaultFirstRecord
                .LastRecord = wdDefaultLastRecord
            End With
            .Execute Pause:=False
        End With
        
    wdDoc.Application.Visible = True
    
    
    
    
    wdDoc.Close SaveChanges:=False
    
    
    Set wdDoc = Nothing
    
    
    End With
    
    
    End Sub

  • #2
    Re: Trying to run an Automated Mail Merge from Excel Via Word

    Create a mailmerge maindocument in Word; link it to the Excel-'database', design it's layout (the mergefields included) and save it.

    Now the only thing you have to do in Excel is:

    Code:
    Sub M_snb()
      with getobject("G:\OF\mailmerge_maindocument.docx")
         .mailmerge.execute
         .close 0
      end with
    End Sub

    Comment


    • #3
      Re: Trying to run an Automated Mail Merge from Excel Via Word

      Thank you for your reply, Ive tried the code this morning and it gave me a run time error, "word cannot merge documents that can be distributed by mail or fax without a valid mail address. Choose the Setup button to select a mail address data field."

      Is there a way of specifying to use the logged in users email account?

      thanks for your help!

      Comment


      • #4
        Re: Trying to run an Automated Mail Merge from Excel Via Word

        Yes that is part of setting up the mailmerge maindocument.

        Comment


        • #5
          Re: Trying to run an Automated Mail Merge from Excel Via Word

          Is there a way of automating the whole process with a click of a button?

          thank you for the reply!

          Comment


          • #6
            Re: Trying to run an Automated Mail Merge from Excel Via Word

            See post #2

            Comment


            • #7
              Re: Trying to run an Automated Mail Merge from Excel Via Word

              All macros can be automated by a click of a button. The trick is to make the right code to fit your needs.

              If you search for ".Destination = wdSendToEmail " you should be able to find several examples.

              My example attachment shows a few things you might be able to use. Module2 is just an example from a search above which has parts you can use.
              Attached Files

              Comment


              • #8
                Re: Trying to run an Automated Mail Merge from Excel Via Word

                Thank you Kenneth your code has been a great help, i think i have adapted my code but when trying to run the code i get a "Run time error 9105 String is longer than 255 characters" Is this message referring to my data file our the Code is over 255 characters? Do you know how i get round this?

                Code:
                 With wdApp.Documents("O:\Accounts\Gamma\GiftAid\GiftAidLettersTest.docx").MailMerge
                                .OpenDataSource Name:= _
                                "O:\Accounts\Gamma\GiftAid\GiftAidTestingCSV.csv" _
                                , ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
                                AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
                                WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
                                Format:=wdOpenFormatAuto, Connection:= _
                        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=O:\Accounts\Gamma\GiftAid\GiftAidTestingCSV.csv;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Lock" _
                        , SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:= _
                        wdMergeSubTypeAccess
                
                
                                
                                .Destination = wdSendToEmail
                                .MailAddressFieldName = "email"
                                .MailAsAttachment = False
                                .MailFormat = wdMailFormatHTML
                                .MailSubject = "Automated Test"
                                .SuppressBlankLines = True
                                With .DataSource
                                    .FirstRecord = wdDefaultFirstRecord
                                    .LastRecord = wdDefaultLastRecord
                                    End With
                 .Execute Pause:=False
                                End With
                            wdApp.ActiveDocument.Save
                            wdApp.ActiveDocument.Close

                Comment


                • #9
                  Re: Trying to run an Automated Mail Merge from Excel Via Word

                  I have managed to resolve this issue, it was due to the whole connection string being to long, i did a bit of pruning with it and its sent an automated email!!

                  Comment


                  • #10
                    Re: Trying to run an Automated Mail Merge from Excel Via Word

                    Will it be fairly straight forward to send the email to my Outlook Outbox? As a bit of a fail safe before the live emails are sent??

                    Comment


                    • #11
                      Re: Trying to run an Automated Mail Merge from Excel Via Word

                      I don't have time to code it but maybe parts of this sort of thing would help. Check to see if the MailMerge object has a similar outbox feature.
                      Code:
                      ' http://www.vbforums.com/showthread.php?445722-Send-emial-to-my-outlook-outbox
                          Option Explicit
                          'Add a reference to MS Outlook xx.0 Object Library
                          Private Sub Command1_Click()
                              Dim oApp As Outlook.Application
                              Dim oOutbox As Outlook.MAPIFolder
                              Dim oEmail As Outlook.MailItem
                              Set oApp = New Outlook.Application
                              Set oOutbox = oApp.GetNamespace("MAPI").GetDefaultFolder(olFolderOutbox)
                              Set oEmail = oApp.CreateItem(olMailItem)
                              With oEmail
                                  .To = "[email protected]"
                                  .CC = "[email protected]"
                                  .Subject = "Spam - Meow!!!"
                                  .BodyFormat = olFormatPlain
                                  .Body = "Blah, blah, blah..."
                                  .Importance = olImportanceHigh
                                  .ReadReceiptRequested = True
                                  .Recipients.ResolveAll
                                  .Save
                                  .Move oOutbox
                              End With
                              Set oEmail = Nothing
                              Set oOutbox = Nothing
                              oApp.Quit
                              Set oApp = Nothing
                          End Sub

                      Comment


                      • #12


                        Re: Trying to run an Automated Mail Merge from Excel Via Word

                        Thanks again Kenneth, im pretty sure i can use osme of that code. I will have a play on Monday.

                        Thanks
                        Aron

                        Comment

                        Working...
                        X