edit code for e-mail

[fvd]

Hi,

I use the code below to send e-mails, but i would like to make some changes to it.
First problem is that i send e-mails to different people, let's say 10 persons, but i need to click on the "Send"-button in Outlook for every e-mail (so 10 times).
What do i have to change in this code to send them in one time?

Second thing i would like to change is to use the BCC-field instead of the To-field because now everybody can see the other recipients...

But i'm not familiar with VBA, i'm affraid, so a little help would be great!!!


Private Sub CmdEmail_Click()
Dim rsEmail As DAO.Recordset
Dim strEmail As String
Set rsEmail = CurrentDb.OpenRecordset("QrySendEmails")

Do While Not rsEmail.EOF
strEmail = rsEmail.Fields("Email").Value
DoCmd.SendObject , , , strEmail, , , _
"Subject", _
"Good Afternoon" & _
vbCrLf & vbCrLf & "Your Message.", True

rsEmail.MoveNext

Loop
Set rsEmail = Nothing

MsgBox "Emails have been sent"
End Sub


Thanks already guys!

 

[reclusivemonkey]

The SendObject command has the following syntax;

SendObject (ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile) so you can easily edit your code to use Bcc. The outlook problem, I am not 100% sure but AFAIK, there is no way round this other than to install a third party app which can alter this behaviour. You should be able to find threads mentioning this on here.

 

[fvd]

re

Thanks for the tips, but as i said previous, i'm a total beginner with VBA, so could you tell me a bit more specific how i should edit the code to use the BCC field?

And about the other question of mine: i know there are ways to put all the mail-adresses in the To or BCC field separated with ";".
Does anyone have an idea on how to adjust the code i posted in my first message?

Thanks again!!!
fvd

 

[AcaBrian]

SendObject (ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile)

If you set the EditMessage value to 'False' outlook will not open for editing. It will send the email(s) to the outbox directly
If you want to send one e-mail to many recipients at once, loop through the query first and build a string that contains all the email addresses and puts the string variable in To, CC, or BCC positions depending on how you want them sent.

Your 'Do While' loop should contain something like this
strEmail = rsEmail.Fields("Email").Value & ";"
Then after dropping out of the loop send the email
Also notice I put strEmail in the BCC position in this example

Private Sub CmdEmail_Click()
Dim rsEmail As DAO.Recordset
Dim strEmail As String
Set rsEmail = CurrentDb.OpenRecordset("QrySendEmails")

Do While Not rsEmail.EOF
strEmail = rsEmail.Fields("Email").Value & ";"

rsEmail.MoveNext

Loop

DoCmd.SendObject , , , , ,strEmail , _
"Subject", _
"Good Afternoon" & _
vbCrLf & vbCrLf & "Your Message.", False

Set rsEmail = Nothing

MsgBox "Emails have been sent"
End Sub

 

[Surjer]

If its the same message to everyone you could do this so you only have to click ok once in Outlook...

Private Sub CmdEmail_Click()
Dim rsEmail As DAO.Recordset
Dim strEmail As String
Set rsEmail = CurrentDb.OpenRecordset("QrySendEmails")

Do While Not rsEmail.EOF
strEmail = strEmail & ";" & rsEmail.Fields("Email").Value

rsEmail.MoveNext
Loop

DoCmd.SendObject , , , , ,strEmail , _
"Subject", _
"Good Afternoon" & _
vbCrLf & vbCrLf & "Your Message.", True
Set rsEmail = Nothing

MsgBox "Emails have been sent"
End Sub

just realized this is the same as the last post. (Sorry)

 

[AcaBrian]

Sorry my code needs to remove the final semi-colon with this line

strEmail = Left$(strEmail, Len(strEmail) - 1)


Private Sub CmdEmail_Click()
Dim rsEmail As DAO.Recordset
Dim strEmail As String
Set rsEmail = CurrentDb.OpenRecordset("QrySendEmails")

Do While Not rsEmail.EOF
strEmail = rsEmail.Fields("Email").Value & ";"

rsEmail.MoveNext

Loop
strEmail = Left$(strEmail, Len(strEmail) - 1)
DoCmd.SendObject , , , , ,strEmail , _
"Subject", _
"Good Afternoon" & _
vbCrLf & vbCrLf & "Your Message.", False

Set rsEmail = Nothing

MsgBox "Emails have been sent"
End Sub