Link to home
Start Free TrialLog in
Avatar of Jean-Jacques CELMA
Jean-Jacques CELMAFlag for France

asked on

Excel macro : save an outlook 2010 mail message to a folder

Dear All,

I am using that VBA script from Excel macro to create a mail message with subject, HTML body and attachement file.  I will need when the user click on send the script save the mail message in a mail.msg to a folder. How can I achieve that from Excel macro?

Sub Transmettre_AS()

    Dim ol As Outlook.Application
    Dim message As Outlook.MailItem

    Set ol = New Outlook.Application
        
    Set message = ol.CreateItem(olMailItem)
      
    With message
    .Subject = "INSTRUCTIONS - " & Sheets("Conv.AS").Range("B98").Value
    .To = Sheets("Conv.AS").Range("L100").Value
    .CC = "servicet@contoso.com"
    .BodyFormat = olFormatHTML
    .HTMLBody = "Bonjour," & "<br>" & "Veuillez trouver en PJ les instructions aux AS en cours." & "<br>" & "<br>" & Sheets("Conv.AS").Range("A98").Value & "<br>" & "<br>" & "Merci pour votre collaboration - Thanks for your collaboration"
    .Attachments.Add "\\myserver\service$\SENDINGS\\services INSTRUCTIONS.xlsx"
    .Display

    
    End With

End Sub

Open in new window

Avatar of Jeff Tennessen
Jeff Tennessen
Flag of United States of America image

You can leverage the Application_ItemSend event for this purpose. In the VBA editor, open up the code for ThisOutlookSession and paste the following:

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    SaveMessage Item, "<path>", "<name>.msg"
End Sub

Open in new window


Just replace <path> and <name> with the appropriate values.
Avatar of Jean-Jacques CELMA

ASKER

Hi Jeff,

I did'nt have a chance to test it. But quick question does this will apply to all item send?. I mean when I am creating a new email message that has no relation with the above does the outlook macro will apply to it?
I just want the macro apply to the messsage with the HTML body mentionned above

Thks

JJC
ASKER CERTIFIED SOLUTION
Avatar of Jeff Tennessen
Jeff Tennessen
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Jeff,

I was a little bit busy and did not answer to your question "Can you explain a little more about what should trigger the save?".
The Excel macro open outook, add a subject (always the sale subject), add comment in the body and add an excel file as attachment
the user can also add more comments while the outlook message is opened before clicking on send.
We need to also save that message in a specify network share folder for future tracking before sending or when we click on the send button (no idea if this is possible)
The macro must apply to this email subject only not all emails users are sending

JJC