Sending Email "with" Attachments

In a previous post,  the code shown below was given to open MS Outlook and pre-fill the "To:" field with Email Addresses listed in a query.  

I would now like to know how to modify this code so the Email (Outlook) would include an attachment.   The attachment would either be an Access report (example: rpt_MyReport) and display as a WORD document; or the attachment would be a list of data from a query (example: qry_MyData) and display as an Excel document.   To clarify, I want these to be "attachments."

Please note if there are any property/setting changes within Access that will be required to allow permissions for the code to run.

----------
Private Sub Command3_Click()
  fncEmail
End Sub

 Function fncEmail() As Boolean

Dim rst As ADODB.Recordset
Dim strTo As String
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient

Set rst = New ADODB.Recordset
rst.Open "SELECT Email FROM qry_Email", CurrentProject.Connection

'/now create the outlook message
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

Do Until rst.EOF
  strTo = strTo & rst("Email") & ";"
  rst.MoveNext
Loop

With objOutlookMsg
  Set objOutlookRecip = .Recipients.Add(strTo)
  objOutlookRecip.Type = olTo
  .Subject = "Email Subject"
  '/.Body = "Body Here"
  .Display '/Note: to automatically send the email without previewing, change this to .Send
End With

Set objOutlook = Nothing
Set objOutlookMsg = Nothing
Set objOutlookRecip = Nothing
Set rst = Nothing

End Function

lstadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

harfangCommented:
After the line:

  '/.Body = "Body Here"

Add:

   .Attachments.Add "C:\Path\FileName.Ext"

This was the easy part... Now about creating the file...
0
harfangCommented:
To create an excel file, you should be able to do something like:

       DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, TableName:="Table/Query Name", FileName:="C:\Path\FileName.xls"

In my experience, this fails on two machines out of three: "Couln't find installable ISAM". You might be able to solve the problem  by doing a full reinstall, but I ended up dropping that "feature" alltogether...

It is possible, and rather easy, to write a basic "QueryToExcel" VB routine. You might want to search here (and elsewhere) if you find something that suits your needs.

Good Hunting!
0
flavoCommented:
Can be done much eaiser, this is becasue you are sending objects from Access..

Set rst = New ADODB.Recordset
rst.Open "SELECT Email FROM qry_Email", CurrentProject.Connection

Do Until rst.EOF
  strTo = strTo & rst("Email") & ";"
  rst.MoveNext
Loop

If Len(strTo) > 0 then
     strTo = Left(strTo, len(strTo) -1)
     DoCmd.SendObject acSendReport, "rtpMyReport", acFormatRTF, strTo, , , "My Subject", "Body Text", 0 'report as RTF
     DoCmd.SendObject acSendQuery, "qSelMyQuery", acFormatXLS, strTo, , , "My Subject", "Body Text", 0 'Query results as XLS
else
    'no-one to send to
  msgbox "no ppl to send to...", vbcritical, "nooo!!!"
end if

Idea??

Dave
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

harfangCommented:
Correction: To create an excel file, there is a better solution...

    DoCmd.OutputTo acOutputQuery, "Table/Query Name", acFormatXLS, "C:\Path\FileName.xls"

The same can be used for a word document (nothing fancy, no boxes, no borders, etc):

    DoCmd.OutputTo acOutputReport, "Report name", acFormatRTF,"C:\Path\FileName.rtf"


This should get you started.
0
flavoCommented:
For reports, i suggest sending a snap shot of the report (like a pdf, free and all, but you need to get the viewer from MS - FREE)  keps the format just like the way it looked when preview printing

DoCmd.SendObject acSendReport, "rtpMyReport", "Snapshot Format (*.snp)", strTo, , , "My Subject", "Body Text", 0 'report as SNP
0
flavoCommented:
Re link 1 - The fist seems like it MAY be the only one to come into play
re link 2 - get the SP
re link 3 - NA
0
lstadAuthor Commented:
I receive an error message.   "Ambiguous name detected: fncEmail"

I have two command buttons.  The First button runs the code I submitted above which just populates Outlook with the email addresses.

The Second button is running the addtional code provided above to insert an "Access" report or query as an attachment in Outlook.  The format for the report should be Word and the format for the query should be Excel.

I inserted the code above as shown below (between ***)

Private Sub Command3_Click()
  fncEmail
End Sub

 Function fncEmail() As Boolean

Dim rst As ADODB.Recordset
Dim strTo As String
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient

Set rst = New ADODB.Recordset
rst.Open "SELECT Email FROM qry_Email", CurrentProject.Connection

'/now create the outlook message
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

Do Until rst.EOF
  strTo = strTo & rst("Email") & ";"
  rst.MoveNext
Loop


********************
If Len(strTo) > 0 then
     strTo = Left(strTo, len(strTo) -1)
     DoCmd.SendObject acSendReport, "rpt_MyReport", acFormatRTF, strTo, , , "My Subject", "Body Text", 0 'report as RTF
    else
    'no-one to send to
  msgbox "no ppl to send to...", vbcritical, "nooo!!!"
end if

*******************

With objOutlookMsg
  Set objOutlookRecip = .Recipients.Add(strTo)
  objOutlookRecip.Type = olTo
  .Subject = "Email Subject"
  '/.Body = "Body Here"
  .Display '/Note: to automatically send the email without previewing, change this to .Send
End With

Set objOutlook = Nothing
Set objOutlookMsg = Nothing
Set objOutlookRecip = Nothing
Set rst = Nothing

End Function
0
flavoCommented:
Function fncEmail() As Boolean

Dim rst As ADODB.Recordset
Dim strTo As String
Set rst = New ADODB.Recordset
rst.Open "SELECT Email FROM qry_Email", CurrentProject.Connection

Do Until rst.EOF
  strTo = strTo & rst("Email") & ";"
  rst.MoveNext
Loop


********************
If Len(strTo) > 0 then
     strTo = Left(strTo, len(strTo) -1)
     DoCmd.SendObject acSendReport, "rpt_MyReport", acFormatRTF, strTo, , , "My Subject", "Body Text", 0 'report as RTF
    else
    'no-one to send to
  msgbox "no ppl to send to...", vbcritical, "nooo!!!"
end if


Set rst = Nothing

End Function
0
flavoCommented:
make that

Function fncEmail() As Boolean

Dim rst As ADODB.Recordset
Dim strTo As String
Set rst = New ADODB.Recordset
rst.Open "SELECT Email FROM qry_Email", CurrentProject.Connection

Do Until rst.EOF
  strTo = strTo & rst("Email") & ";"
  rst.MoveNext
Loop


********************
If Len(strTo) > 0 then
     strTo = Left(strTo, len(strTo) -1)
     DoCmd.SendObject acSendReport, "rpt_MyReport", acFormatRTF, strTo, , , "My Subject", "Body Text", 0 'report as RTF
    else
    'no-one to send to
  msgbox "no ppl to send to...", vbcritical, "nooo!!!"
end if

rst.close
Set rst = Nothing

End Function
0
lstadAuthor Commented:
Making some progress.
-
The code above works.   However, it sends the Email with attachment without displaying it for preview.  I want Outlook to open and allow me to view the addresses and attachments "before" I send it.  This will also allow for the adding of text comments in the email.  
-
Second problem.... I had to delete the original code (see initial post) to get this to run.  I want both options.  Option#1: Open blank Outlook Email with addresses prefilled [accomplished using original code].   Option #2, #3, #4 etc... send via Outlook , prefills Emal addresses, and includes attachments as predefined reports - but not to send them without displaying Outlook [Send will be manually controlled].

0
flavoCommented:
change this

   DoCmd.SendObject acSendReport, "rpt_MyReport", acFormatRTF, strTo, , , "My Subject", "Body Text", 0 'report as RTF

to

   DoCmd.SendObject acSendReport, "rpt_MyReport", acFormatRTF, strTo, , , "My Subject", "Body Text", -1 'report as RTF


to open a blank email you could also use

   DoCmd.SendObject acSendnoObject, , acFormatRTF, strTo, , , "My Subject", "Body Text", -1 'report as RTF
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lstadAuthor Commented:
I was able to get this to work, but if the user decides to cancel out of the outlook message, without sending, it sends a VBA Error pop up.  I will have others using this database and I do not want them to have such easy access to the code.  Is there anything we can add to this code so it will not send the VBA Error message?   And, how would I hard code an email address for the "CC:" within this code.




Function fncEmail() As Boolean

Dim rst As ADODB.Recordset
Dim strTo As String
Set rst = New ADODB.Recordset
rst.Open "SELECT Email FROM qry_Email", CurrentProject.Connection

Do Until rst.EOF
  strTo = strTo & rst("Email") & ";"
  rst.MoveNext
Loop

If Len(strTo) > 0 then
     strTo = Left(strTo, len(strTo) -1)
     DoCmd.SendObject acSendReport, "rpt_MyReport", acFormatRTF, strTo, , , "My Subject", "Body Text", -1 'report as RTF
    else
    'no-one to send to
  msgbox "no ppl to send to...", vbcritical, "nooo!!!"
end if

rst.close
Set rst = Nothing

End Function
0
harfangCommented:

After "Dim strTo As String" Add:

   On Error Goto mncEmail_Error

Before "End Function" Add:

   Exit Function

   fmcEmail_Error:
   ' Inspect and Manage Error here, using the Err Object
   Err.Clear
   Exit Function

As it stands, this will exit without any sort of message ... I's probably  best to write instead:

   fmcEmail_Error:
   ' Inspect and Manage Error here, using the Err Object
   Select Case Err.Number
      Case 123, 456, 89   ' no message for these error numbers
      Case Else
         MsgBox "Error in fncEmail:" & vbCrLf & vbCrLf & Err.Description, , "Error nb " & Err.Number
   End Select
   Err.Clear
   Exit Function

This way, you will see messages for unexpected errors (arn't they all?) and then add the error number to the list of "expected" errors, like user cancelling...


As for CC:, it's the parameter after strTo.

At the top of the function add:

   Const cCopyTo = "Mommy <mother@home.sky>"

And in the SenObject command, replace ", strTo, , ," with ", strTo, cCopyTo, ,"


That shoud do it. Hope it works!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.