Send email from access vba using outlook (1 Viewer)

armesca

Registered User.
Local time
Today, 02:20
Joined
Apr 1, 2011
Messages
45
I have code that works well below getting email addresses from a query and sending it. However, I would like to make all the recipients Bcc. If anyone could suggest some tweaks to my code below, I would appreciate it:

Private Sub cmd_email_del_Click()
On Error GoTo error
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim MyBodyText As String
Dim rsemail As DAO.Recordset
Dim ns As Outlook.NameSpace
Dim Folder As Outlook.MAPIFolder
Dim mysql As String
Subjectline$ = "Communication from GPD Training Branch"
DoCmd.SetWarnings False
Set MyOutlook = New Outlook.Application
Set MyOutlook = CreateObject("Outlook.Application")
Set ns = MyOutlook.GetNamespace("MAPI")
Set Folder = ns.GetDefaultFolder(olFolderInbox)
MyOutlook.Explorers.Add Folder
Set db = CurrentDb()
mysql = "SELECT DISTINCT tbl_employees.Email FROM (tbl_employees INNER JOIN tbl_employee_courses ON tbl_employees.E_ID = tbl_employee_courses.E_ID) INNER JOIN tbl_courses ON tbl_employee_courses.C_ID = tbl_courses.C_ID WHERE (((tbl_employee_courses.End_Date)<Now()) AND ((tbl_employee_courses.Status) Not In ('Completed','Canceled')));"
Set rsemail = db.OpenRecordset(mysql)

Set MyMail = MyOutlook.CreateItem(olMailItem)

Do Until rsemail.EOF

'this allows you to send one email to multiple recipients
MyMail.Recipients.Add rsemail(0)

'MyMail.To = rsemail(0) 'maillist("email")

'And on to the next one...
rsemail.MoveNext

Loop

'This gives it a subject
MyMail.Subject = Subjectline$
MyMail.Body = "Hello, " & Chr(13) & Chr(13) & "You are receiving this email because our records indicate you may have completed a course and have yet to submit your course certificate." & Chr(13) & Chr(13) & "Thank You"
MyMail.Display
Set MyMail = Nothing
Set MyOutlook = Nothing
DoCmd.SetWarnings True
rsemail.Close
db.Close
Set db = Nothing


Exit Sub
error:
Call globalerrorhandler
 

GinaWhipp

AWF VIP
Local time
Today, 02:20
Joined
Jun 21, 2011
Messages
5,901
Have you tried...

MyMail.BCC = rsemail(0) 'maillist("email")
 

G37Sam

Registered User.
Local time
Today, 10:20
Joined
Apr 23, 2008
Messages
454
If that doesn't work, try CDO email sending method
 

armesca

Registered User.
Local time
Today, 02:20
Joined
Apr 1, 2011
Messages
45
MyMail.BCC does work, but only for sending an email to one person, this defeating the purpose of the BCC. Is there a way to send to multiple people and BCC them?
 

G37Sam

Registered User.
Local time
Today, 10:20
Joined
Apr 23, 2008
Messages
454
Add them to your "email" string separated by semicolons
 

GinaWhipp

AWF VIP
Local time
Today, 02:20
Joined
Jun 21, 2011
Messages
5,901
Hmmm, that should have worked... Let me ask another question, did the code work with the .To? OR was that the point it wasn't looping?
 

Acke

Registered User.
Local time
Today, 07:20
Joined
Jul 1, 2006
Messages
158
This is all I need now. Thanks guys!!!

However, I do not understand how to:

Add them to your "email" string separated by semicolons

Could you please explain how to add multiple addresses to the BCC?
 

aliT

Registered User.
Local time
Yesterday, 23:20
Joined
Jul 31, 2013
Messages
10
With outlook 2010 you need to use the MailItem.SentOnBehalfOfName field to set the sender field to a certain email address. Took me ages to find out.
 

willknapp

Registered User.
Local time
Today, 02:20
Joined
Aug 16, 2012
Messages
93
This is all I need now. Thanks guys!!!

However, I do not understand how to:



Could you please explain how to add multiple addresses to the BCC?

In the Do...Loop portion of your code, try this:
Code:
Do Until rsemail.EOF
 
MyMail.BCC = MyMail.BCC & rsemail!Email & ";"
 
'And on to the next one...
rsemail.MoveNext
 
Loop

This way, each address is added with a semi-colon at the end. When the code is looped, the next address is simply appended to the existing datafield. When all is said and done, your MyMail.BCC field will end up looking like this example:

"Name1@Company.com;Name2@Business.com;Name3@School.edu;"

If you're so inclined, you can use a Left statement to remove that last semi-colon, but it's not necessary, as it won't make a difference to Outlook if it's there or not.
 

aliT

Registered User.
Local time
Yesterday, 23:20
Joined
Jul 31, 2013
Messages
10
Now that i used the SentOnBehalfOfName field to send from a different person , the problem is that this ends up in the junk box. Anyone know of a way changing the From email without it going to junk?
 

mobile75

New member
Local time
Today, 07:20
Joined
Jul 25, 2013
Messages
8
Try this code

Private Sub BtnEmail_Click()

Dim vMail, vMailA, vTipo, vNumero As String

vMail = Me.email
vMailA = Me.MAILAGENTE
vTipo = Me.Tipo_Ordine
vNumero = Me.Numero_Ordine
vBCC = Me.Address

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.TO = vMail
.CC = ""
.BCC = vBCC
.Subject = "Conferma ordine " + vTipo + vNumero
.BodyFormat = olFormatHTML
.HTMLbody = "<html><head></head><body>In allegato copia conferma ordine <b>" & vTipo + " " + vNumero & "</b><br><br>Distinti saluti<br><br><b>?..<br><font size=4> </font></br></b></body></html>"
.Attachments.Add "C:\Temp\" + vNumero + ".pdf"
'.Display
.send
End With

DoCmd.Close

End Sub

I use it (and run correctly) with acc2007 and out2007
 

Bar_NZ

Registered User.
Local time
Today, 19:20
Joined
Aug 14, 2012
Messages
48
Can you please tell me where the MailItem.SentOnBehalfOfName goes and what I should use before it please Dim, Set....:banghead:
 

GinaWhipp

AWF VIP
Local time
Today, 02:20
Joined
Jun 21, 2011
Messages
5,901
Bar_NZ,

Please start your own thread so it gets fresh eyes AND more attention.
 

Users who are viewing this thread

Top Bottom