Email BCC and pdf attachment (1 Viewer)

[debbiedoobie10]

I have a table named tblEmailCell that has a field named EmailAddress ( this is the email address I want to use). I have a query named qryMyEmailAddresses that pick 25 email address from tblEmailCell. Could someone help me write VBA to send a email with the EmailAddress in the bcc, the subject to be "Xmas Letter", attach a PDF and put the pdf in the body of the email. Thank you

 

[pr2-eugin]

There are several posts on this Forum on creating a Outlook application object to send email.. Simply look down this post for similar threads.. If they do not answer your question search using the search function in this forum.. To get the address values, you need to create a recordset and then assign it to a string.. I have written a small function to do that..

Public Function getEmails() As String
    Dim bccStr As String
    Dim rstObj As Recordset 
    Set rstObj = CurrentDb.OpenRecordset("qryMyEmailAddresses")
    
    Do While Not rstObj.EOF
        bccStr = bccStr & rstObj.Fields("EmailAddress") & ";"
        rstObj.MoveNext
    Loop
    
    getEmails = Left(bccStr, Len(bccStr)-1)
End Function

so all you need is to assign it to the bcc of the Outlook object...

oulookObjectName.BCC = getEmails()

If you are stuck.. post back..

 

[debbiedoobie10]

Paul,

Do you know the code to attach the pdf to the email and in the body of the email. I need to put the from address in also.

 

[pr2-eugin]

Debbie, did you have a look around this forum as there are several examples... I recently answered a similar query on this thread.. Look in there for how to attach files/add emails..
Complete thread.. http://access-programmers.co.uk/forums/showthread.php?t=238248

 

[nickblitz]

There are several posts on this Forum on creating a Outlook application object to send email.. Simply look down this post for similar threads.. If they do not answer your question search using the search function in this forum.. To get the address values, you need to create a recordset and then assign it to a string.. I have written a small function to do that..

Public Function getEmails() As String
    Dim bccStr As String
    Dim rstObj As Recordset 
    Set rstObj = CurrentDb.OpenRecordset("qryMyEmailAddresses")
    
    Do While Not rstObj.EOF
        bccStr = bccStr & rstObj.Fields("EmailAddress") & ";"
        rstObj.MoveNext
    Loop
    
    getEmails = Left(bccStr, Len(bccStr)-1)
End Function

so all you need is to assign it to the bcc of the Outlook object...

oulookObjectName.BCC = getEmails()

If you are stuck.. post back..

where do insert oulookObjectName.BCC = getEmails() in?

 

[nickblitz]

ok nvm. got it.