Page 1 of 3 123 LastLast
Results 1 to 15 of 38
  1. #1
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557

    Attach a PDF File and a Report to an EMail

    Hi Everyone



    I am trying to use the following Code to generate an Email which includes multiple lines in the Msg Body as well as attach a Report and a PDF File.

    Currently when I run the code I get :-

    1 Email which has the "rptCariProviderLetter" attached plus another EMail which has the Multiple Text lines in the Msg Body.

    It does not attach the PDF file "C:\HowtoCreateCARIAccountV43 & "" &.Pdf"

    I have cross posted this on Utter Access https://www.utteraccess.com/topics/2.../posts/2768314

    Is it possible to achieve the 3 options required in one Email or should I split it into 2 Emails?

    Any help appreciated.

    The Code is:-
    Code:
    Private Sub cmdEMail_Click()
     
    10        On Error GoTo cmdEMail_Click_Error
    20    If Me.Dirty = True Then Me.Dirty = False
          Dim objOutlookAttach As Object
          Dim strWhere As String
          Dim strToWhom As String
          Dim strMsgBody As String
          Dim strSubject As String
          Dim strtxtName As String
          Dim strDocName As String
    30    strDocName = "rptCariProviderLetter"
     
    40    strWhere = "[CustomerID]=" & Me.CustomerID
    50    strSubject = "CARI Stages to be Completed"
    60    strToWhom = Nz(Me![Email])
          '70    strMsgBody = "Find attached details of the next Stage to be completed."
     
    70    Set OutApp = CreateObject("Outlook.Application")
    80    Set OutMail = OutApp.CreateItem(0)
     
    90    On Error Resume Next
     
    100   With OutMail
    110   .To = strToWhom
    120   .Subject = strSubject
     
          Dim mymsg As String
    130   mymsg = "Onboarding Announcement:" & vbCrLf
    140   mymsg = mymsg & "As a part of the Onboarding process, please ensure that the below two-step process" & vbCrLf
     
    150   .body = mymsg
    160   Attachments.Add "C:\HowtoCreateCARIAccountV43 & "" &.Pdf"
     
    170   DoCmd.SendObject acSendReport, "rptCariProviderLetter", acFormatPDF, strToWhom, , , strSubject, strMsgBody, True
     
     
    180       .Display
    190   End With
          '410   Set objApp = Nothing 'if / after you Send it
     
    200       On Error GoTo 0
    210       Exit Sub
    cmdEMailReport_Click_Error:
    220       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdEMailReport_Click of Sub Form_qryInterviewList subform"
     
             
    230       On Error GoTo 0
    240       Exit Sub
     
    cmdEMail_Click_Error:
     
    250       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdEMail_Click, line " & Erl & "."
     
    End Sub
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Attachments.Add "C:\HowtoCreateCARIAccountV43.Pdf"

  3. #3
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi ranman256

    That change had no effect. Still produces 2 Emails but no PDF attachment.

    Do you know how I can change the code so that it generates the EMail Body Text lines Plus the Report letter attachment.

    I think I need to not try and do the PDF attachment in the same process.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  4. #4
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Your code isn't making much sense, you are mixing different methods.

    You are opening an outlook object.
    You then use DoCmd.SendObject - which will open another independent Outlook object (or whatever default email client you have) with just the report attached.

    Then your are trying to add the pdf attachment to the first mail outlook message.

    If the pdf isn't getting added maybe the filename or path are incorrect?
    Because you have on error resume next it won't tell you if it's missing.


    If there are two attachments you need to Attachments.Add twice - once for the PDF and once for the report?

    Get the pdf file and the report created and stored as a pdf file into two full path string variables.
    Then create your outlook mail and have two lines ;

    Attachments.Add strMyPDF_File
    Attachments.Add strMyReport_PDF_File

    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    As an old COBOL A/P the full stop was always very important. The same apparently applies when using objects in Access.?
    Your Attachments code should be prefixed with a . or the object name. You are using a With statement, so I would expect the former?

  6. #6
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Everyone
    Changed the code to that shown below and now getting the error as shown.

    Code is now:-

    Code:
    Private Sub cmdEMail_Click()
    10        On Error GoTo cmdEMail_Click_Error
    20    If Me.Dirty = True Then Me.Dirty = False
          Dim objOutlookAttach As Object
          Dim strWhere As String
          Dim strToWhom As String
          Dim strMsgBody As String
          Dim strSubject As String
          Dim strtxtName As String
          Dim strDocName1 As String
          Dim FilePath As String
          Dim mymsg As String
    
    
    30    strDocName1 = "rptCariProviderLetter"
    40    FilePath = "C:\PDF Files\HowtoCreateCARIAccountV43"
    
    
    50    strWhere = "[CustomerID]=" & Me.CustomerID
    60    strSubject = "CARI Stages to be Completed"
    70    strToWhom = Nz(Me![Email])
    
    
    80    Set OutApp = CreateObject("Outlook.Application")
    90    Set OutMail = OutApp.CreateItem(0)
    
    
    100   With OutMail
    110   .To = strToWhom
    120   .Subject = strSubject
    
    
    130   mymsg = "Onboarding Announcement:" & vbCrLf
    140   mymsg = mymsg & "As a part of the Onboarding process, please ensure that the below two-step process is completed." & vbCrLf
    
    
    150   .Body = mymsg
    160   Attachments.Add "C:\PDF Files\HowtoCreateCARIAccountV43 .Pdf"
    170   Attachments.Add strDocName1
    
    
    
    
    180       .Display
    190   End With
    
    
    200       On Error GoTo 0
    210       Exit Sub
    cmdEMailReport_Click_Error:
    220       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdEMailReport_Click of Sub Form_qryInterviewList subform"
    
    
              
    230       On Error GoTo 0
    240       Exit Sub
    
    
    cmdEMail_Click_Error:
    
    
    250       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdEMail_Click, line " & Erl & "."
    End Sub
    Attached Thumbnails Attached Thumbnails error.JPG  
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  7. #7
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    There is an extra space in the file name, before the file extension ?

    160 Attachments.Add "C:\PDF Files\HowtoCreateCARIAccountV43 .Pdf"
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #8
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Minty

    Removed the space and still the same error
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  9. #9
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Put the file path into a string variable outside the outlook automation as suggested and try that, I've experienced weirdness with the outlook object not accepting direct references.

    In general I would also check that the file existed, and wasn't open before getting into the trying to attach it, it just avoids any issues.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  10. #10
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by Minty View Post
    Put the file path into a string variable outside the outlook automation as suggested and try that, I've experienced weirdness with the outlook object not accepting direct references.

    In general I would also check that the file existed, and wasn't open before getting into the trying to attach it, it just avoids any issues.
    Minty,
    O/P already has the file attached in another forum in post #14
    https://www.utteraccess.com/topics/2059927/posts/2768346#14

  11. #11
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I have just looked at the other site post and the file path is different again, so I'm not surprised things aren't working.

    @Mike - I've just noticed you have over 14,000 posts at UA! , how can this be causing you a problem?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  12. #12
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Minty

    I may have 14,000 posts but my knowledge of VBA is very limited.

    The other post that WelshGasMan is referring to is me just attempting to attach just the PDF File.

    This code attaches the File but it does not enter the EMail address of the Subject as shown below:-







    Code:
    Private Sub cmdPDF_Click()
    
    
    10        On Error GoTo cmdPDF_Click_Error
          Dim strSQL As String
          Dim objOutlookAttach As Object
          Dim objApp As Object
          Dim objMailItem As Object
          Dim strAttachment As String
          Dim strMailItem As String
          Dim strWhere As String
          Dim strToWhom As String
          Dim strMsg As String
          Dim strSubject As String
    
    
    20    strWhere = "[CustomerID]=" & Me.CustomerID
    30    strToWhom = Me.Email
    40    strSubject = "PDF Guide"
    50    strMsg = "Find attached details of CARI Setup Process"
    60    Set objApp = CreateObject("outlook.application")
    70    Set objMailItem = objApp.CreateItem(0)
    80    objMailItem.Attachments.Add ("C:\HowtoCreateCARIAccountV43.pdf")
    90    objMailItem.Display 'change to .Display if you want to just display it.
           
    100       On Error GoTo 0
    110       Exit Sub
    
    
    cmdPDF_Click_Error:
    
    
    120       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdPDF_Click, line " & Erl & "."
    
    
    End Sub
    Attached Thumbnails Attached Thumbnails Blank EMail.JPG  
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  13. #13
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    @Mike
    Try to wrap the attachments in parenthesis:

    Code:
     .Body = mymsg
     .Attachments.Add ("C:\PDF Files\HowtoCreateCARIAccountV43.Pdf")
     .Attachments.Add (strDocName1)  'this needs to be the full path and file name of the pdf version of the report; so first you need to export the report as PDF using DOcmd.OutputTo then set the strDocName1 to the full name of the file (including path)
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  14. #14
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Yet, previous incarnations of your code did?

    https://www.utteraccess.com/topics/2059927/posts/2768356#4

    Quote Originally Posted by mike60smart View Post
    Hi Minty

    I may have 14,000 posts but my knowledge of VBA is very limited.

    The other post that WelshGasMan is referring to is me just attempting to attach just the PDF File.

    This code attaches the File but it does not enter the EMail address of the Subject as shown below:-







    Code:
    Private Sub cmdPDF_Click()
    
    
    10        On Error GoTo cmdPDF_Click_Error
          Dim strSQL As String
          Dim objOutlookAttach As Object
          Dim objApp As Object
          Dim objMailItem As Object
          Dim strAttachment As String
          Dim strMailItem As String
          Dim strWhere As String
          Dim strToWhom As String
          Dim strMsg As String
          Dim strSubject As String
    
    
    20    strWhere = "[CustomerID]=" & Me.CustomerID
    30    strToWhom = Me.Email
    40    strSubject = "PDF Guide"
    50    strMsg = "Find attached details of CARI Setup Process"
    60    Set objApp = CreateObject("outlook.application")
    70    Set objMailItem = objApp.CreateItem(0)
    80    objMailItem.Attachments.Add ("C:\HowtoCreateCARIAccountV43.pdf")
    90    objMailItem.Display 'change to .Display if you want to just display it.
           
    100       On Error GoTo 0
    110       Exit Sub
    
    
    cmdPDF_Click_Error:
    
    
    120       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdPDF_Click, line " & Erl & "."
    
    
    End Sub

  15. #15
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Hi Mike,

    I'm not sure if you have subsequenlty fixed this, but you are setting a variable to the email address, but never using it?
    The same with other variables.

    e.g. strToWhom = Me.Email
    But you haven't then set the outlook part

    objMailitem.to = strToWhom
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 10-19-2017, 02:31 PM
  2. Replies: 5
    Last Post: 04-27-2015, 12:26 PM
  3. Replies: 5
    Last Post: 09-05-2014, 12:06 PM
  4. Attach Report to Email
    By teebumble in forum Reports
    Replies: 5
    Last Post: 11-28-2012, 05:42 PM
  5. Replies: 2
    Last Post: 12-28-2011, 09:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums