Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sending Email "with" Attachments

Posted on 2004-10-27
14
Medium Priority
?
360 Views
Last Modified: 2011-10-03
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

0
Comment
Question by:lstad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 58

Expert Comment

by:harfang
ID: 12429798
After the line:

  '/.Body = "Body Here"

Add:

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

This was the easy part... Now about creating the file...
0
 
LVL 58

Expert Comment

by:harfang
ID: 12429858
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
 
LVL 34

Expert Comment

by:flavo
ID: 12429875
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 58

Expert Comment

by:harfang
ID: 12429913
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
 
LVL 34

Expert Comment

by:flavo
ID: 12429945
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
 
LVL 34

Expert Comment

by:flavo
ID: 12429958
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
 

Author Comment

by:lstad
ID: 12430060
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
 
LVL 34

Expert Comment

by:flavo
ID: 12430086
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
 
LVL 34

Expert Comment

by:flavo
ID: 12430088
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
 

Author Comment

by:lstad
ID: 12430777
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
 
LVL 34

Accepted Solution

by:
flavo earned 1600 total points
ID: 12430809
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
 

Author Comment

by:lstad
ID: 12440189
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
 
LVL 58

Assisted Solution

by:harfang
harfang earned 400 total points
ID: 12441252

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question