cdo email using a query (1 Viewer)

[sammers101]

Is it possible with CBO to reference a report or a form with something like the following?

.TextBody = "customerComics"

Currently I am attaching my report as an attachment but I would prefer to have it in the actual email instead. I'm using runtime access. I was also having problems adding a loop to DoCmd.GoToRecord , , acNext

My current code:

DoCmd.OutputTo acOutputReport, "customerComics", acFormatPDF, "C:\delete\pulls.pdf"

On Error GoTo errHandler

Dim cdoConfig As Object
Dim cdoMessage As Object

'create email object
Set cdoConfig = CreateObject("CDO.Configuration")
Set cdoMessage = CreateObject("CDO.Message")

'setup server configuration
With cdoConfig.Fields
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = "465"
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = "1"
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = "2"
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = "-1"
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = "60"


    'following settings are optional
    '.Item("http://schemas.microsoft.com/cdo/configuration/smtpaccountname") = "The DB Guy"
    '.Item("http://schemas.microsoft.com/cdo/configuration/sendemailaddress") = "thedbguy@gmail.com"
    '.Item("http://schemas.microsoft.com/cdo/configuration/smtpuserreplyemailaddress") = "thedbguy@gmail.com"
  
    'warning: it's a security risk to hard-code your username and password
  
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "username"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
  
    .Update
End With


'build and send email message
With cdoMessage
    Set .Configuration = cdoConfig
    .From = "username@gmail.com"
    .to = Me.fldEmail
    '.Cc = "copy@email.address"
    '.Bcc = "blind.copy@email.address"
    .Subject = "Sub"
    .TextBody = ""
    '.HTMLBody = "<h2>Use this to send email as HTML.</h2>"
    .AddAttachment "C:\delete\pulls.pdf"
    .Send
End With

DoCmd.GoToRecord , , acNext


errExit:
    Set cdoConfig = Nothing
    Set cdoMessage = Nothing
    Exit Sub
  
errHandler:
    MsgBox Err.Number & ": " & Err.Description
    Resume errExit

 

[June7]

No, cannot just reference form or report to embed in email body. Open and loop a recordset object to build string for embedding data within email body. Review

https://stackoverflow.com/questions...et-to-insert-html-table-in-outlook-email-body

https://bytes.com/topic/access/answers/959625-need-vba-sending-email-query-result-recordset

Another approach is to convert report to an embeddable image - jpg, bmp, png.

Why do you need to loop form records?

 

[theDBguy]

Hi. Another possible approach is to use acFormatHTML and then read it into a String variable for your email's body.

How to Read a Text File

Invariably, at one point or another in your journey to write VBA in Access, there will come a time when you will need to read the contents o...

thedbguy.blogspot.com

 

[sammers101]

Not sure what the best way would be to do it. All of these options work during runtime?

As far as the loop I am sending an email to each customer with a list of comics they are receiving. Then I go to the next customer and do it again, for each customer.

 

[June7]

I would loop the form's RecordsetClone. And within that loop, build string for embedding into email body. If all data is available on the form, then it will be in the RecordsetClone and just reference its fields to build string. If not and data is multiple records of a related table, then open a recordset of that customer's data and loop that recordset (loop within a loop). Can use HTML code tags to format data into tabular structure.

Fairly common topic and many examples. Roughly:

Dim rs As DAO.Recordset, strBody As String
With Me.RecordsetClone
    Do While Not .EOF
        Set rs = CurrentDb.OpenRecordset("SELECT * FROM tablename WHERE CustomerID = " & !CustomerID)
        Do While Not rs.EOF
            strBody = strBody & rs!FieldName1 & "," & rs!FieldName2 & "," & rs!FieldName3 & vbCrLf
            rs.MoveNext
        Loop
        '------
        'code here to set email elements and send email
        '------
        rs.Close
        .MoveNext
        strBody = ""
    Loop
End With

Review https://www.access-programmers.co.uk/forums/threads/send-email-in-html-format.39098/