By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,805 Members | 1,640 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,805 IT Pros & Developers. It's quick & easy.

Send an Email BCC only

100+
P: 185
I use this public function:

Expand|Select|Wrap|Line Numbers
  1. Public Function SendAnEmail(olSendTo As String, _
  2.                             olSubject As String, _
  3.                             olEMailBody As String, _
  4.                             olDisplay As Boolean, _
  5.                    Optional olCCLine As String, _
  6.                    Optional olBCCLine As String, _
  7.                    Optional olOnBehalfOf As String, _
  8.                    Optional olAtchs As String, _
  9.                    Optional SendAsHTML As Boolean) As Boolean
  10. On Error GoTo EH
  11.     Dim olApp       As Outlook.Application
  12.     Dim olMail      As Outlook.MailItem
  13.     Dim strArray()  As String
  14.     Dim intAtch     As Integer
  15.  
  16.     Set olApp = CreateObject("Outlook.Application")
  17.     Set olMail = olApp.CreateItem(olMailItem)
  18.     With olMail
  19.         .To = olSendTo
  20.         .subject = olSubject
  21.  
  22.         If SendAsHTML Then
  23.             .BodyFormat = olFormatHTML
  24.             .HTMLBody = olEMailBody
  25.         Else
  26.             .body = olEMailBody
  27.         End If
  28.  
  29.         .CC = olCCLine
  30.         .BCC = olBCCLine
  31.         .SentOnBehalfOfName = olOnBehalfOf
  32.         strArray = Split(olAtchs, "%Atch")
  33.  
  34.         For intAtch = 0 To UBound(strArray)
  35.             If FileExists(strArray(intAtch)) Then _
  36.                 .Attachments.Add strArray(intAtch)
  37.         Next intAtch
  38.  
  39.         If olDisplay Then
  40.             .Display
  41.         Else
  42.             .Send
  43.         End If
  44.  
  45.     End With
  46.     Set olMail = Nothing
  47.     Set olApp = Nothing
  48.  
  49.     SendAnEmail = True
  50.  
  51.     Exit Function
  52. EH:
  53.     MsgBox "There was an error generating the E-Mail!" & vbCrLf & vbCrLf & _
  54.         "Error: " & Err.Number & vbCrLf & _
  55.         "Description: " & Err.Description & vbCrLf & vbCrLf & _
  56.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  57.     SendAnEmail = False
  58.     Exit Function
  59. End Function
Which is called out in this sub routine:

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnGroupEmail_Click()
  2.  
  3. On Error GoTo EH
  4.  
  5.     Dim strSendTo     As String
  6.     Dim strSubject    As String
  7.     Dim strEMailBody  As String
  8.     Dim strCCLine     As String
  9.     Dim strBCCLine    As String
  10.     Dim strOnBehalfOf As String
  11.     Dim MyDB          As DAO.Database
  12.     Dim rstEMail      As DAO.Recordset
  13.     Dim strEMail      As String
  14.  
  15.     DoCmd.SetWarnings False
  16.     DoCmd.OpenQuery "qryEmail", acViewNormal
  17.     DoCmd.SetWarnings True
  18.  
  19. Set MyDB = CurrentDb
  20.  
  21. Set rstEMail = MyDB.OpenRecordset("Select * From tblEMail", _
  22.     dbOpenSnapshot, dbOpenForwardOnly)
  23.  
  24.    With rstEMail
  25.             If Not (.BOF And .EOF) Then
  26.                 Call .MoveFirst
  27.                 Do While Not .EOF
  28.                     'Build the Recipients String
  29.                     strSendTo = _
  30.                         strSendTo & _
  31.                         IIf(strSendTo = "", _
  32.                             "", _
  33.                             ";") & !Email
  34.                   Call .MoveNext
  35.                 Loop
  36.             End If
  37.             Call .Close
  38.         End With
  39.  
  40.         Call MyDB.Close
  41.         Set rstEMail = Nothing
  42.         Set MyDB = Nothing
  43.  
  44.     'Generate and Display the E-Mail
  45.  
  46.         Call SendAnEmail(olSendTo:=strSendTo, _
  47.                      olSubject:=strSubject, _
  48.                      olEMailBody:=strEMailBody, _
  49.                      olDisplay:=True, _
  50.                      SendAsHTML:=True)
  51.  
  52.     Exit Sub
  53. EH:
  54.     MsgBox "There was an error sending mail!" & vbCrLf & vbCrLf & _
  55.         "Error: " & Err.Number & vbCrLf & _
  56.         "Description: " & Err.Description & vbCrLf & vbCrLf & _
  57.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  58.     Exit Sub
  59.  
  60. End Sub
My question is "How Would I send this as BCC: only"?

Tried changing the strSendto string to strBCCLine string in all instances and all that happens is a blank email.
Feb 21 '20 #1

✓ answered by twinnyfo

Expand|Select|Wrap|Line Numbers
  1. Call SendAnEmail(olSendTo:="", _
  2.                  olSubject:=strSubject, _
  3.                  olEMailBody:=strEMailBody, _
  4.                  olBCCLine:=strSendTo, _
  5.                  olDisplay:=True, _
  6.                  SendAsHTML:=True)
You should be better able to troubleshoot these by now, DJ....

Share this Question
Share on Google+
5 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,387
DJ,

Easy:

Expand|Select|Wrap|Line Numbers
  1.         Call SendAnEmail(olBCCLine:=strSendTo, _
  2.                      olSubject:=strSubject, _
  3.                      olEMailBody:=strEMailBody, _
  4.                      olDisplay:=True, _
  5.                      SendAsHTML:=True)
You are confusing your variables with your arguments.

Hope this hepps!
Feb 24 '20 #2

100+
P: 185
So I changed that, but now it doesn't compile. It tells me

"Argument not optional" in this section:

Expand|Select|Wrap|Line Numbers
  1.  Call SendAnEmail(olBCCLine:=strSendTo, _
  2.                      olSubject:=strSubject, _
  3.                      olEMailBody:=strEMailBody, _
  4.                      olDisplay:=True, _
  5.                      SendAsHTML:=True)
Feb 24 '20 #3

twinnyfo
Expert Mod 2.5K+
P: 3,387
Expand|Select|Wrap|Line Numbers
  1. Call SendAnEmail(olSendTo:="", _
  2.                  olSubject:=strSubject, _
  3.                  olEMailBody:=strEMailBody, _
  4.                  olBCCLine:=strSendTo, _
  5.                  olDisplay:=True, _
  6.                  SendAsHTML:=True)
You should be better able to troubleshoot these by now, DJ....
Feb 24 '20 #4

100+
P: 185
Thanks Twinny,

Still finding some of this stuff confusing.
Feb 24 '20 #5

twinnyfo
Expert Mod 2.5K+
P: 3,387
No worries, DJ!

Keep pluggin' away. Soon things will start to become natural. It takes many years to become comfortable with this stuff. You'll get there! Just stay encouraged and keep asking questions!
Feb 24 '20 #6

Post your reply

Sign in to post your reply or Sign up for a free account.