Jean-Jacques CELMA
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?
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
Open in new window
Just replace <path> and <name> with the appropriate values.