none
How can I send a Document as a single E-mail that has had a Mail-Merge Data Source attached? RRS feed

  • Question

  • This is a work-around for another problem that I don't seem to be able to get an answer to. But I've got problems with the work-around too.

    What I actually want to do is be able to extract the Attachments from a Document.MailEnvelope, so that I can re-attach them to Individual E-mail waiting in OUTLOOK's Outbox after they have been created by a Merge to E-mail.  I gave up on a couple of direct approaches when I found out that the .Pathname property of an Item never has anything in it.  It was then suggested that the MailItem.SaveAsFile should work.  But that only works for an OUTLOOK MailItem, not a WORD Document.MailEnvelope.Item.

    So now I'm being sneaky.  What I'm attempting to do is 'Send a Copy' of the master Document to OUTLOOK's Outbox. I can then open it as an OUTLOOK MailItem and extract the Attachments from there.  All this works - provided I use the User Interface to  'Send a Copy'.  BUT if I automate it in VBA using:
         ActiveDocument.MailEnvelope.Item.Send
    it runs the Merge - despite the fact that I've done a
         MailMerge.DataSource.Close and a
         MailMerge.MainDocumentType = wdNotAMailMerge

    Does anyone else have any ideas?

    Here's my code.  Most of it as you can see is actually working.

    Private Sub CopyAttachmentsToTemporaryFolder()
    '
    '   Written by: Brian McGuigan
    '           of: On2it Software Ltd
    '      Version: 1
    '        Dated:
    'First Used in: NEXT Version
    '       Status: Being Tested
    '
    '      Purpose: This Subroutine copies Attachments in MailEnvelope into s_TemporaryFolder
    '               so that they can then be re-attached to every Document in Outlook's Outbox.
    
    '       Method: Because of the problems associated with extracting Attachment Pathnames or Files
    '               from Word's MailEnvelope, this Subroutine:
    '                   1. Sends the Master E-mail Document complete with Attachments to OUTLOOK_Outbox
    '                      where it will wait as Outlook is currently OFF-Line.
    '                      It will be the only E-mail in the Outbox,as we've just made sure it's empty.
    '                   2. Opens the only E-mail in the Outbox as an OUTLOOK_MailItem.
    '                   3. Saves all the OUTLOOK_MailItem.Attachments into s_TemporaryFolder
    '                   4. Deletes the OUTLOOK_MailItem from the OUTLOOK_Outbox as we don't really want to send it.
    '                      All we want to do is extract its Attachments using .SaveAsFile,
    '                      which only works with an OUTLOOK_MailItem not the WORD ActiveDocument.MailEnvelope
    '
        Dim i_Attachment As Integer
    
        Dim s_AttachmentFileName As String
        Dim s_ExcelDataSourcePathname As String
        Dim s_TemporaryFilePathname As String
        Dim s_TemporaryFolder As String
        
        On Error GoTo ErrorHandler
    
        With ActiveDocument.MailMerge
            s_ExcelDataSourcePathname = .DataSource.Name    'so it can be re-opened afterwards
    'UNTESTED CODE is BELOW here <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
            .DataSource.Close                               'to Try to avoid Word trying to complete the Merge on .Send
            .MainDocumentType = wdNotAMergeDocument         'to Try to avoid Word trying to complete the Merge on .Send
    'UNTESTED CODE is ABOVE here <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        End With
        
        'Make sure the MailEnvelope is Visible so that we can edit its Controls
        ActiveDocument.ActiveWindow.EnvelopeVisible = True
        
        'Send Master E-mail Document complete with Attachments to Outlook's Outbox
        With ActiveDocument.MailEnvelope.Item
            .To = "Somebody@Somewhere.com"  'Doesn't matter what this is,
                                            'as it won't actually be sent E-mail will be deleted first.
                                            
    'UNTESTED CODE is BELOW here <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    '       SendKeys ("%S")     'Tried using SendKeys <Alt>+S to Click 'Send a Copy' Button
                                'SendKeys didn't work because <Alt>+S does not work in the User Interface either!
                                'Could not find a key combination that did either.
                                'So we'll have to use .Send but .Close the MailMerge.DataSource beforehand
                                'and then re-open the MailMerge Data Source afterwards.
                                
    'DEBUG COMMENT:
            'This STILL Triggers the merge even after I've closed the MailMerge.DataSource
            'Tried resetting .MainDocumentType = wdNotAMergeDocument. That didn't work either.
            'Tried recording a Macro when I clicked 'Send A Copy, but nothing was recorded.
            .Send               'This triggers the Merge.
    'UNTESTED CODE is ABOVE here <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
            
        End With
        
        'Open the only E-mail in the OUTLOOK_Outbox as an OUTLOOK_MailItem
        Set OUTLOOK_MailItem = OUTLOOK_Outbox.Items(1)
        
        'Save all the OUTLOOK_MailItem.Attachments into s_TemporaryFolder
        With OUTLOOK_MailItem.Attachments
            For i_Attachment = 1 To .Count
            
                s_AttachmentFileName = .Item(i_Attachment).FileName
                
                'Attachments also include image001.* - image999.* files,
                'which hopefully we don't need to attach as they will be in the Merge E-mails anyway.
                'So we don't need to Save them.
                If Left(s_AttachmentFileName, 5) <> "image" Then
                    s_TemporaryFilePathname = _
                        g_s_FolderContainingActiveDocument & _
                        "\Mail-Merge Attachments\" & _
                        s_AttachmentFileName
                    .Item(i_Attachment).SaveAsFile (s_TemporaryFilePathname)
                End If
                
            Next i_Attachment
        End With
        
        'Delete the E-mail from the OUTLOOK_Outbox, as we don't want it there when we do the Merge
        OUTLOOK_MailItem.Delete 'Might need to use Redemption
        
        'Re-open MailMerge Data Source
        With ActiveDocument.MailMerge
            .MainDocumentType = wdEMail
            .OpenDataSource _
                Name:=s_ExcelDataSourcePathname, _
                ReadOnly:=False, _
                SQLStatement:="SELECT * FROM " & g_s_ExcelSheetName
        End With
        
    NormalExit:
        Exit Sub
        
    ErrorHandler:
        'This code is executed whenever an Error is detected during execution of this routine
    
        'Log Error using standard routine
        WriteErrLog "[On2it - Merge to E-mail].CopyAttachmentsToTemporaryFolder", Err.Number, Err.Description
        
        GoTo NormalExit
    
        Resume              'This Statement is left here for use during De-Bugging only.
                            'It is impossible to reach during normal execution.
                            'Just Drag the Execution Point to here and Press F8.
                            'Execution will resume at the instruction that caused the Error.
    End Sub
    

    Thursday, July 16, 2015 1:25 AM

Answers

  • Hi Brian,

    The MailEnvelope.Item property returns a MailItem object, do you try to get attachments by using that MailItem object?

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 17, 2015 6:26 AM
    Moderator

All replies

  • Hi Brian,

    Could you provide the detail steps that do it through UI?

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, July 16, 2015 8:49 AM
    Moderator
  • Hi Starain,

    I performed the following steps in Word and Outlook 2007 without my Macros being involved:

    1. Open blank Word Document
    2. Click ‘Send to Mail Recipient’ in shortcut Toolbar to expose mail envelope.
    3. Go to ‘Mailings’ Tab
    4. Start Mail Merge’ as ‘E-mail Messages’. 
    5. Select Recipients and attach an Excel Spreadsheet as a Mail-Merge data source. 
    6. Open Outlook.
    7. Click File, Work Offline
    8. Return to Word
    9. Enter an E-mail Address and Subject
    10. Click ‘Send a Copy’.  A single E-mail will be sent to your Outlook Outbox – without a Merge being performed.
    11. Open Outlook.  Your E-mail will be waiting in your Outbox.
    12. Toggle File, Work Offline.  Your E-mail will be released from the Outbox.

    When I attempt the same thing in VBA ActiveDocument.MailEnvelope.Item.Send always tries to do a Merge.

    Regards
    Brian

    Thursday, July 16, 2015 1:19 PM
  • Hi Brian,

    The MailEnvelope.Item property returns a MailItem object, do you try to get attachments by using that MailItem object?

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 17, 2015 6:26 AM
    Moderator
  • Hi Starain,

    I thought I had tried what you suggested previously, but before replying, I thought I'd better give it another go so that I could report exactly what happens.  However this time I persisted a little longer and got it to work.  The code below does the job:

        'Save all the OUTLOOK_MailItem.Attachments into g_s_AttachmentsFolderPathname
        g_s_AttachmentsFolderPathname = _
            g_s_FolderContainingActiveDocument & _
            "\Mail-Merge Attachments"
    
         With ActiveDocument.MailEnvelope.Item
             For i_Attachment = 1 To .Attachments.Count
                s_AttachmentFileName = .Attachments(i_Attachment).FileName
                s_AttachmentPathname = _
                    g_s_AttachmentsFolderPathname & "\" & _
                    s_AttachmentFileName
                .Attachments(i_Attachment).SaveAsFile (s_AttachmentPathname)
             Next i_Attachment
          End With
    
    MANY, MANY Thanks
    Brian

    Friday, July 17, 2015 12:09 PM