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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
flavoConnect With a Mentor Commented:
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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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
 
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
 
harfangConnect With a Mentor Commented:

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
All Courses

From novice to tech pro — start learning today.