Here we go - that took no time at all because I found a Microsoft site where all the hard work has been done. Plagiarise - that's the name of the game ("Let no one else's work escape your eyes ... but please to always call it research." Tom Lehrer)
Two things to do:
- From the VBA coding window click on Tools in the menu bar and then select References. Scroll down until you find an entry for Microsoft Outlook. Click the checkbox and then OK.
- Copy and paste the following procedure into your own db. (Indentation has gone crazy no no matter.)
Code:
Sub NewMailMessage(Optional varSubject As Variant, _
Optional varAttachment As Variant)
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookAttach As Outlook.Attachment
' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
' Set the Subject of the message.
If Not IsMissing(varSubject) Then
.Subject = CStr(varSubject)
End If
' Add attachments to the message.
If Not IsMissing(varAttachment) Then
Set objOutlookAttach = .Attachments.Add(varAttachment)
End If
End With
objOutlookMsg.Display
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub
I would suggest you place it in a module - it just makes things a little clearer. You may then use it from within your own db by:
Code:
<modulename>.NewMailMessage varSubject:="Once upon a time.", varAttachment:="C:\Users\Rod\Gnomes.xlsx"
Replace the blue terms with your own names.
Notes:
- This opens a new Outlook session ignoring any other sessions that might be open at the time.
- There is no validation of the arguments. Both are optional so you may pass none, one or both.
- There is no error handling. Beware if a run-time error occurs it is likely to leave a hidden Outlook session running. Solve this by rebooting.
Get back to me if you are interested in refining the process.
PS. Does it create a new session? I'm now no longer sure and can't prove it either way.