Solved

Sending Email "with" Attachments

Posted on 2004-10-27
351 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
Question by:lstad
    14 Comments
     
    LVL 58

    Expert Comment

    by:harfang
    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
    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
    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
     
    LVL 58

    Expert Comment

    by:harfang
    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 41

    Expert Comment

    by:shanesuebsahakarn
    0
     
    LVL 34

    Expert Comment

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

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    860 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now