VBA Email Method: BCC Only 2

[cstuart79]

Need some help figuring out how to adapt the following code to keep To: blank and add email addresses to Bcc: instead....

Private Sub Command432_Click()
Dim strHighAddress As String
Dim strResetnow As String
strHighAddress1 = Concatenate("SELECT Email1 FROM CONTACTS WHERE GroupEmail = True", ";")
strHighAddress2 = Concatenate("SELECT Email2 FROM CONTACTS WHERE GroupEmail = True", ";")
strResetnow = "Update CONTACTS SET GroupEmail = False;"
DoCmd.SetWarnings False
Application.FollowHyperlink "mailto:" & "" & strHighAddress1 & "" & "" & strHighAddress2 & ""
DoCmd.RunSQL strResetnow
DoCmd.SetWarnings False
Me!CONTACTS.Requery
End Sub

 

[kjv1611]

I assume you're running this in Access or Excel?

If so, I'd suggest looking at using the Outlook object, as it will give you the sort of control you're after. Then you won't be using the follow-hyperlink method, but rather you'll build the email piece by piece - to/cc/bcc/subject/body/attachments.

There's also a good Outlook forum where you'll find really good information. It's ... well, I can't seem to remember or find it, b/c I've referred to it so rarely.... hmm...

This one looks like it might be a good resource (first time finding it):

http://www.howto-outlook.com/

Ah, here it is. It's specifically for helping with coding in Outlook:

http://www.outlookcode.com/

So in case you don't get an answer that fits your situation here, which after 7 days and no response looks like a possibility... try this site.

If you do find a solution, please post what you find back in this thread. However, I'm pretty sure you just need to use the Outlook object.

 

[kjv1611]

Hey, whaddya know...

Check this out:

http://www.outlookcode.com/article.aspx?id=72

Found via this Google search.

 

[kjv1611]

Yeah, I just noticed that I was in the Access Forms forum, not VBA: Visual Basic for Applicaitons (Microsoft) forum..

So now I'm REALLY assuming Access.

 

[PHV]

Have a look here:
thread705-1369407

http://www.paulsadowski.com/WSH/cdo.htm

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cstuart79]

can't i just adjust the above code to add Bcc similar to below? or is there no way to use application.followhyperlink for bcc:?

Private Sub Command432_Click()
Dim strHighAddress As String
Dim strResetnow As String
strHighAddress1 = Concatenate("SELECT Email1 FROM CONTACTS WHERE GroupEmail = True", ";")
strHighAddress2 = Concatenate("SELECT Email2 FROM CONTACTS WHERE GroupEmail = True", ";")
strResetnow = "Update CONTACTS SET GroupEmail = False;"
DoCmd.SetWarnings False
Application.FollowHyperlink "mailto: "support@getcollc.com""
Application.FollowHyperlink "mailbcc:" & "" & strHighAddress1 & "" & "" & strHighAddress2 & ""
DoCmd.RunSQL strResetnow
DoCmd.SetWarnings False
Me!CONTACTS.Requery
End Sub

 

[PHV]

You may try something like this:
Application.FollowHyperlink "mailto:support@getcollc.com?bcc=" & strHighAddress1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cstuart79]

perfect...thanks so much!

 

[cstuart79]

only 1 bug encountered....if email address has "&" in it (ie. P&S@abc.com) then the string breaks. any ideas on how to escape & ?

 

[PHV]

Application.FollowHyperlink "mailto:support@getcollc.com?bcc=" & Replace(strHighAddress1, "&", "%26)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cstuart79]

i am using the following:

Application.FollowHyperlink "mailto: support@getcollc.com?bcc=" & "" & strHighAddress1 & "" & "" & strHighAddress2 & ""

where should "replace" clause go?

 

[kjv1611]

cstuart79, it's pretty straight forward... just think about what the replace command/function is doing, and you'll get it sorted out. But for the sake of time for now:

Application.FollowHyperlink "mailto: support@getcollc.com?bcc=" & [b][blue]Replace([/blue][/b]"" & strHighAddress1 & "" & "" & strHighAddress2 & ""[blue][b],"&","%26")[/b][/blue]

I'm assuming PHV accidentally left off the end quote on the "%26) entry. If I'm wrong, it'll be pretty obvious when you get an error.

 

[kjv1611]

And if that won't work, either way, I suppose you could use:

Application.FollowHyperlink "mailto: support@getcollc.com?bcc=" & Replace("" & strHighAddress1 & "" & "" & strHighAddress2 & "","&",[blue]Chr(38)[/blue])

Chr(38) is the ascii character for the ampersand(&)

You can look up any ascii character codes by searching for ascii. This is usually the first hit on Google:

http://www.asciitable.com/

 

[cstuart79]

thanks a mil guys! works perfectly...that missing " was what was throwing me off!