none
Forward an outlook email from an excel macro RRS feed

  • Question

  • Dear members,

    I am trying to forward an outlook email to multiple adresses with a vba macro in excel.

    Why in excel ? Because all the email adresses are in the excel file

    I want to forward an email that I received. I want to forward the exact same email (i.e. without the usual lines when you click "forward" in outlook

    From: Martin Dupont
    Sent: Thursday, November 08, 2018 4:37 PM
    To:
    Martin Dupont
    Subject: Test
    Importance: High

    For now, I have the following code:

    Sub Forward_Current_Email()
    
        Dim outForward As Outlook.MailItem
        
        Set outForward = ActiveExplorer.Selection.item(1)
        
        outForward.Display
        outForward.To = "martin.dupont@hotmail.fr"
        outForward.Send
    
    End Sub

    But with this code I can't manipulate the body of the email (i.e. I can't delete the lines that appears when you forward an email).

    Thanks for your help.

    Yoan


    • Edited by Yoan_Geneva Thursday, November 8, 2018 4:14 PM
    Thursday, November 8, 2018 4:12 PM

All replies

  • It would probably be better run from Outlook as it is easier to address the selected message from Outlook and read the required data from Excel, however provided you have the message selected in Outlook's message list in the navigation pane, the following should work for you to create an exact duplicate of the message that can be forwarded to the value from your worksheet in place of

    .To = "someone@somewhere.com"

    Note that you have to access Outlook correctly or it simply won't work. For that you also need the function from

    http://www.rondebruin.nl/win/s1/outlook/openclose.htm

    You can restore the line

    .Send

    immediately after the line

    .Display (which must be retained)

    after testing.

    Option Explicit
    
    Sub SendOnMessage()
    'Graham Mayor - http://www.gmayor.com - Last updated - 09 Nov 2018
    'Requires the following function to start Outlook correctly, or it won't work
    'http://www.rondebruin.nl/win/s1/outlook/openclose.htm
    Dim wdDoc As Object
    Dim olItem As Object
    Dim olOutMail As Object
    Dim olInsp As Object
    Dim oRng As Object, oNewRng As Object
    Dim OutApp As Object
        Set OutApp = OutlookApp()
    
        Set olItem = OutApp.ActiveExplorer.Selection.Item(1)
        With olItem
            Set olInsp = .GetInspector
            Set wdDoc = olInsp.WordEditor
            Set oRng = wdDoc.Range
        End With
        Set olOutMail = OutApp.CreateItem(0)
        With olOutMail
            .To = "someone@somewhere.com"
            .Subject = olItem.Subject
            Set olInsp = .GetInspector
            Set wdDoc = olInsp.WordEditor
            Set oNewRng = wdDoc.Range
            oNewRng.formattedtext = oRng.formattedtext
            .Display
    '.Send ' End With lbl_Exit: Set olItem = Nothing: Set olOutMail = Nothing Set wdDoc = Nothing: Set oRng = Nothing: Set oNewRng = Nothing Set olInsp = Nothing Set OutApp = Nothing Exit Sub End Sub



    Graham Mayor - Word MVP
    www.gmayor.com



    Friday, November 9, 2018 4:57 AM
  • Hi Graham

    Thank you very much for your reply!

    I only have an error (run time error 287) with the "Set wdDoc = olInsp.WordEditor"

    I think this is linked with the references. I tried to fix the problem but didn't find the solution for now.

    Do you have any idea ?

    Thank you

    Yoan

    Friday, November 9, 2018 2:27 PM
  • Dis you add the function from

    http://www.rondebruin.nl/win/s1/outlook/openclose.htm

    that I mentioned twice? It won't work without it.


    Graham Mayor - Word MVP
    www.gmayor.com

    Saturday, November 10, 2018 4:21 AM
  • Yes I added the function

    The bug comes from Set wdDoc = olInsp.WordEditor

    "Application-defined or object-defined error"

    Thanks again

    Tuesday, November 13, 2018 12:27 PM
  • If OutApp.IsTrusted 

    returns false
    Tuesday, November 13, 2018 4:46 PM
  • Plan B

    Try the following in Excel

    Sub SendOnMessage()
    'Graham Mayor - http://www.gmayor.com - Last updated - 09 Nov 2018'
    Dim wdDoc As Object
    Dim olItem As Object
    Dim olOutMail As Object
    Dim olInsp As Object
    Dim oRng As Object, oNewRng As Object
    Dim OutApp As Object
        On Error Resume Next
        Set OutApp = GetObject(, "Outlook.Application")
        If Err <> 0 Then
            MsgBox "Outlook must be running and the message selected first!"
            GoTo lbl_Exit
        End If
    
        Set olItem = OutApp.ActiveExplorer.Selection.Item(1)
        With olItem
            Set olInsp = .GetInspector
            Set wdDoc = olInsp.WordEditor
            Set oRng = wdDoc.Range
        End With
        Set olOutMail = OutApp.CreateItem(0)
        With olOutMail
            .To = "someone@somewhere.com"
            .Subject = olItem.Subject
            Set olInsp = .GetInspector
            Set wdDoc = olInsp.WordEditor
            Set oNewRng = wdDoc.Range
            oNewRng.formattedtext = oRng.formattedtext
            .Display
            '.Send '
        End With
    
    lbl_Exit:
        Set olItem = Nothing: Set olOutMail = Nothing
        Set wdDoc = Nothing: Set oRng = Nothing: Set oNewRng = Nothing
        Set olInsp = Nothing
        Set OutApp = Nothing
        Exit Sub
    End Sub


    Graham Mayor - Word MVP
    www.gmayor.com


    Wednesday, November 14, 2018 5:10 AM
  • Thanks Graham. The bug is fixed now!

    But the objects wdDoc and ORng are empty (Nothing) so the body of the new mail is empty.

    Do you know why ?

    Thanks again for your help

    Wednesday, November 14, 2018 8:22 AM
  • I take it this is the PC version of Office, and you have a message selected in the navigation window?

    If you add

    On Error Goto 0
    before
     Set olItem = OutApp.ActiveExplorer.Selection.Item(1)

    It should indicate where the problem lies, which, dare I say, could be the same error as before.


    Graham Mayor - Word MVP
    www.gmayor.com

    Wednesday, November 14, 2018 11:35 AM
  • Yes same error,

    Set wdDoc = olInsp.WordEditor

    "Application-defined or object-defined error"

    I am on Microsoft Office 2013.

    you have a message selected in the navigation window? Yes

    Wednesday, November 14, 2018 12:07 PM