CDO emails

[Soma_rich]

Hi I have got my emails working fine and I can add an atachment from a drive, but how do I add a query or a report as an attachment? Using teh sendobject function its simple but i cant quite get it to work with CDDO.

Dim cdoConfig
Dim msgOne
Dim colAttachFiles() As String


Set cdoConfig = CreateObject("CDO.Configuration")
With cdoConfig.Fields
.Item(cdoSendUsingMethod) = cdoSendUsingPort
.Item(cdoSMTPServerPort) = 25
.Item(cdoSMTPServer) = "my server"
.Update
End With

Set msgOne = CreateObject("CDO.Message")
Set msgOne.Configuration = cdoConfig
msgOne.To = "my email"
msgOne.From = "ICDB"
msgOne.Subject = "Test Email"
msgOne.TextBody = Forms![form1].[Text0]
msgOne.AddAttachment "C:\dir2.txt"

msgOne.Send

Any ideas?

 

[John.Woody]

What I do is use the outputTo command and output the report to html format, attach that file and send it.

HTH
John

 

[Soma_rich]

Thanks John,
I was thinking about doing just that, but i didnt think it was very "neat" as I would end up with a load of files on my C:\ drive.

Not really an issue just wanted to keep it all in the code.
Cheers
Richard

 

[John.Woody]

You can use code to delete the file after use

 

[Soma_rich]

Cheers

I have got around it by populating a recordset with a query result and putting that in a variable.

Much neater.

Dim cdoConfig
Dim msgOne
Dim address As String
Dim icdb As Database
Dim rst As Recordset
Dim SQL As String
Dim body As String
Dim RST2 As Recordset

SQL = "select email from Users where PasswordRecipient = true ;"
body = "SELECT TopoJobDetails.TargetAAPass, TopoJobDetails.ActualAAPass, Suppliers.SupplierName, TopoJobDetails.JobNumber  FROM Suppliers INNER JOIN TopoJobDetails ON Suppliers.SupplierID = TopoJobDetails.Supplier WHERE (((TopoJobDetails.ActualAAPass)>[TopoJobDetails]![TargetAAPass]));"

Set icdb = CurrentDb()
Set rst = icdb.OpenRecordset(SQL, dbOpenDynaset)
Set RST2 = icdb.OpenRecordset(body, dbOpenDynaset)

If rst.BOF Then
    address = rst.Fields("email")
    rst.MoveNext
Else
 Do Until rst.EOF
        address = address & ";" & rst.Fields("email")
        rst.MoveNext
   Loop
End If

Set cdoConfig = CreateObject("CDO.Configuration")
With cdoConfig.Fields
.Item(cdoSendUsingMethod) = cdoSendUsingPort
.Item(cdoSMTPServerPort) = 25
.Item(cdoSMTPServer) = "my server"
.Update
End With

Set msgOne = CreateObject("CDO.Message")
Set msgOne.Configuration = cdoConfig
msgOne.To = address
msgOne.From = "ICDB"
msgOne.Subject = "Test Email"
msgOne.TextBody = body


msgOne.Send

rst.Close
Set rst = Nothing

icdb.Close
Set icdb = Nothing

End Sub