Emailing From access (adding selected recipients to bcc line of outlook new mail wind

[enigma54]

Hi,

Hope you guys can help here. I have learnt a lot from this forum in the past.

I would class myself as a VBA beginner. I am starting to learn the basics but most of my code is harvested from souces on the internet and then modified if necessary!

I have a database that holds customer email addresses in a table (tblCustomer). Currently I have a form (FrmSendEmail) based on a query (qrySendEmail) that shows a list of all customers that have an email address.

The form simply has 3 fields. Customer | CustomerEmail | SendEmail.

The Send Email field is a checkbox.

What I would like is to select (on the form) all the customers I want, click a button and an outlook new mail message will appear with all the names I selected (from the query) inserted into the BCC field. I can then add a message etc and other appropriate information and send the message manually.

All the examples I have found on the internet cycle through a recordset and send emails (customised or not) to individual recipients. I cant find an example where all the email addresses from the recordset are added to the email.

I can provide a sample database if necessary

Thanks a lot

Dave

 

[enigma54]

On further searching it appears that I may be able to achieve this using the Docmd.sendobject method but loop though the recipients creating a comma separated string which is then put into the BCC field.

I am slightly stuffed as to how though!

Hopefully someone can help me out

Thanks

Dave

 

[enigma54]

Epic Fail!

I clearly suck at searching! After googling a little more I finally found this:

http://www.access-programmers.co.uk/forums/showthread.php?t=58212

Exactly what I wanted ...grrrr

Hopefully this may point someone else on the right track!

 

[jonno_g]

Beware the typo's in the code in the thread you link to if you are going to copy & paste, namely "Dim db As DAO.Datebase" and "Dim rs As DAO.Recordet" - both of which will throw errors.

It was not identified in that thread, but they were at least part of the OP's problems.

 

[enigma54]

Thanks for that

I noticed when i first ran the code and checked for typos

I still hang my head that I didnt notice the post sooner though

Thanks anyway

 

[pnevilm]

Do you have a similar solution to sending multiple reports in a single email? I am using MS Access 2007 with MS Outlook. I have looked EVERYWHERE!!!!

 

[enigma54]

Hi,

Is this what you want. I found it on an EE article here:

http://www.google.co.uk/#hl=en&biw=...q=f&aqi=&aql=&oq=&gs_rfai=&fp=81aa0f6582b4550

(2nd result down)

Function sndrpt()
Rem <!-- Make sure the Microsoft Object xx.0 Reference Library is enabled & _
(found under Tools>References in the VBA Editor) -->
 
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim strAttach1 As String
Dim strAttach2 As String
Dim strAttach3 As String
Dim strAttach4 As String
Dim strAttach5 As String
 
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)
 
'Output Reports
Rem <!-- change "Reportx" to match the report names you wish to export. & _
IMPORTANT: Make sure the location you select to save your reports to exists, Access will & _
not create the folders for you. -->
DoCmd.OutputTo acOutputReport, "Report1", acFormatRTF, "C:\YourFolder\Report1.rtf", False
DoCmd.OutputTo acOutputReport, "Report2", acFormatRTF, "C:\YourFolder\Report1.rtf", False
DoCmd.OutputTo acOutputReport, "Report3", acFormatRTF, "C:\YourFolder\Report1.rtf", False
DoCmd.OutputTo acOutputReport, "Report4", acFormatRTF, "C:\YourFolder\Report1.rtf", False
DoCmd.OutputTo acOutputReport, "Report5", acFormatRTF, "C:\YourFolder\Report1.rtf", False
 
'Set Attachments
Rem <!-- make sure to correlate the attachments to each of the reports you wish to send -->
strAttach1 = "C:\YourFolder\Report1.rtf"
strAttach2 = "C:\YourFolder\Report2.rtf"
strAttach3 = "C:\YourFolder\Report3.rtf"
strAttach4 = "C:\YourFolder\Report4.rtf"
strAttach5 = "C:\YourFolder\Report5.rtf"
 
'Generate email
With objEmail
    .To = "[EMAIL="email@removed.com"]email@removed.com[/EMAIL]"
    .Subject = "Your subject here"
    .Body = "Message in body of email here"
    .Display
    .Attachments.Add strAttach1
    .Attachments.Add strAttach2
    .Attachments.Add strAttach3
    .Attachments.Add strAttach4
    .Attachments.Add strAttach5
End With
 
'Remove attachments from drive
Kill strAttach1
Kill strAttach2
Kill strAttach3
Kill strAttach4
Kill strAttach5
 
End Function