0

I have a macro in Excel that creates multiple email drafts with Excel attachments. I am trying to get around having to allow access to Microsoft Outlook and avoid the below box: enter image description here

I want to include the default email signature in my Outlook account in these emails (multiple users run this macro, so the signatures would vary). When I use the below code with no body in my email, it works and saves the email as a draft with the appropriate signature, however, if I try to add a body to my email, the signature disappears.

Sub SendWorkBookRXFile()

Dim OutlookApp As Object
Dim OutlookMail As Object
Dim signature As String
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
Dim d As Date
    d = Date
If Weekday(d) = vbMonday Then
    d = d - 3
Else
    d = d - 1
End If

With OutlookMail
    .Display
    .To = "someone@company.com"
    .CC = "omeoneelse@company.com"
    .subject = "Daily Pharmacy File / Claims Invoiced"
    .Attachments.Add "attaching a file from my local drive"
    .Close olSave
End With
Set OutlookMail = Nothing
Set OutlookApp = Nothing
End Sub

Does anyone have any ideas on how to avoid the above box, but include a body in the email AND the default signature of the Outlook account?

shoover83
  • 1
  • 1
  • See https://stackoverflow.com/a/32210038/332059 – Dmitry Streblechenko May 15 '23 at 20:49
  • Thank you for the link. I am not quite sure how to incorporate this into my code. I am running my code from an excel file, and am just stumped. I am ok at writing macros, but I am very far from a pro, so I apologize for not understanding. – shoover83 May 16 '23 at 12:43
  • The warning dialog question is likely not connected to the signature question. https://stackoverflow.com/questions/639737/suppress-dialog-warning-that-a-program-is-trying-to-access-my-mails – niton May 16 '23 at 13:33
  • Does this answer your question? [How to add default signature in Outlook](https://stackoverflow.com/questions/8994116/how-to-add-default-signature-in-outlook) – niton May 16 '23 at 13:34
  • I think the "A program is trying to access e-mail address information stored in Outlook" message box has to do with the programmatic access in Outlook set by my organization. I do not have the credentials to change this. The weird part is, if I use the code above in my question, without adding anything to the body of the email, it does NOT bring up that prompt, but it DOES include the default email signature. As soon as I add something in the code for the body of the email, the signature is gone. If I use this piece of code from the suggestions above, it asks for permission: – shoover83 May 16 '23 at 13:48
  • With OMail .Display End With signature = OMail.body – shoover83 May 16 '23 at 13:48
  • Yes, if your computer does not have an Anti Virus app or it is not up to date, you will get a security prompt when reading some sensitize properties, such as `MailItem.Body`. – Dmitry Streblechenko May 16 '23 at 16:14
  • Is there a way to get around this by copying the signature from what is shown when you first .display the email, creating the body of the email, then pasting the copied signature? I'm trying to search for something like this, but I am not having much luck. – shoover83 May 16 '23 at 16:52

0 Answers0