Insert Email Attachment using VBA

[clloyd]

I have an event that creates and email that works perfect however, now I want the code to find a file on our server and insert it as an attachment. I really want to use a pdf version but here is an example of the code calling for a Word document.

The code to call the document is:

 
stDocName = "S:\Misc\Policy\What to have prepared for audit.doc"
 
 

My Send obect code is a follows:

 
DoCmd.SendObject acDocument, stDocName, acFormatRTF, , , , _
 
 

I get an error message that states "The Microsoft.Jet database engine could not find the object 'S:\Misc\Policy\What to have prepared for audit.doc'. Make sure the object exists and that you spell its name and the path name correctly.

Please note the document is located in a different folder on the same network drive that the database resides. I tried to move the document to the same folder as the database and redirect it to that folder but it still did not work.

Any suggestions?

[PianoMan64]

I have an event that creates and email that works perfect however, now I want the code to find a file on our server and insert it as an attachment. I really want to use a pdf version but here is an example of the code calling for a Word document.

The code to call the document is:

 
stDocName = "S:\Misc\Policy\What to have prepared for audit.doc"
 
 

My Send obect code is a follows:

 
DoCmd.SendObject acDocument, stDocName, acFormatRTF, , , , _
 
 

I get an error message that states "The Microsoft.Jet database engine could not find the object 'S:\Misc\Policy\What to have prepared for audit.doc'. Make sure the object exists and that you spell its name and the path name correctly.

Please note the document is located in a different folder on the same network drive that the database resides. I tried to move the document to the same folder as the database and redirect it to that folder but it still did not work.

Any suggestions?

Hey clloyd,

Unforturenetly, SendObject does not support attachments unless, it is a Table, Query, Form, Report, or Code Module to be sent. other than that, it will not support creating an attachment to an email.

If you're wanting to send an email, and say you have outlook installed on your machine, then you can use the following example to send an email. It is a little more involved, but it does support sending any kind of file you want to attach to an email.

 
Dim ObjOutlook as Outlook.Application
Dim ObjOutlookMsg as Outlook.MailItem
Dim objOutlookRecip as Outlook.Recipient
Dim objOutlookAttach as Outlook.Attachment
 
Set ObjOutlook = New Outlook.Application
Set objOutlookMsg = ObjOutlook.CreateItem(olMailItem)
 
With objOutlookMsg
 
     Set ObjOutlookRecip = .Recipients.Add("Name Of Person or Email Address here")
     'repeat previous steps for as many users that you're going to send the email too.
     objOutlookRecip.Type = olTo
     .subject = "Type the subject that you want here"
     Set objectlookAttach = .Attachments.Add("Full Path of File and File Name")
 
     For Each objOutlookRecip in .Recipients
          If Not objOutlookRecip.Resolve Then
               objOutlookMsg.Display
          End if
     Next
     .Send
End with
 

[clloyd]

Thank you for the information. I will see what I can do with this or I may just turn the documents into a report and attach them that way.