Results 1 to 6 of 6
  1. #1
    brownpride is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    10

    Access VBA Optional CC email field

    I have the following code for sending e-mails out. Sometimes the document being sent need to be sent to 2 e-mails (most time there is just 1 e-mail). If there are 2 e-mails in the current code, it will error out because Outlook doesn't immediately recognize the 2 e-mails together (separated b a semicolon). However, if there is 1 e-mail, the code runs and the e-mail sends immediately. I think this is a problem with Outlook because I have all the e-mails stored as contacts.

    I found a way around this problem by having the 2nd e-mail be the CC. However, if there is not a 2nd e-mail, then the code errors because there must be a recognizable e-mail in the CC field.

    Does anyone know some code that will allow the code to complete regardless if there is 1 or 2 e-mails?



    Code:
    Public Sub SendBoth_Click()Dim strDocName As String
    Dim strWhere As String
        strDocName = "ex"
        strWhere = "[Deal ID]=" & Me.Deal_ID
        DoCmd.ApplyFilter , strWhere
        
              Dim objOutlook As Outlook.Application
              Dim objOutlookMsg As Outlook.MailItem
              Dim objOutlookRecip As Outlook.Recipient
              Dim objOutlookAttach As Outlook.Attachment
              Dim fiLename As String, todayDate As String
              Dim Invoice1 As String, Invoice2 As String
              
              Invoice1 = Forms!ex.[Confirmation Contact - E-mail]
              Invoice2 = IIf(Not IsNull(Forms!ex.[Confirmation Contact - E-mail2]), "; " & Forms!ex.[Confirmation Contact - E-mail2], "")
              
              'Export report in same folder as db with date stamp
            todayDate = Format(Date, "mm.dd.yy")
            fiLename = "C:\Users\..."
            DoCmd.OutputTo acForm, "ex", acFormatPDF, fiLename, False
    
    
              ' Create the Outlook session.
              Set objOutlook = CreateObject("Outlook.Application")
    
    
              ' Create the message.
              Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    
    
              With objOutlookMsg
              
                  ' Add the To recipient(s) to the message.
                    Set objOutlookRecip = .Recipients.Add(Invoice1 & Invoice2)
                    objOutlookRecip.Type = olTo
                    
                  ' Add the CC recipient(s) to the message.
                  'Set objOutlookRecip = .Recipients.Add(Invoice2)
                  'objOutlookRecip.Type = olCC
    
    
                 ' Set the Subject, Body, and Importance of the message.
                 .Subject = "Document"
                 
                 .BodyFormat = olFormatHTML
                 .HTMLBody = "<p style='font-size:11pt;font-family:Arial'>" & "Dear " & </p>"
                
                 
                 .Attachments.Add fiLename
    
    
                 ' Add attachments to the message.
    '             If Not IsMissing(AttachmentPath) Then
    '                 Set objOutlookAttach = .Attachments.Add(AttachmentPath)
    '             End If
                
                 ' Resolve each Recipient's name.
                 For Each objOutlookRecip In .Recipients
                     objOutlookRecip.Resolve
                 Next
    
    
                 ' Should we display the message before sending?
                      .Send
    
    
              End With
              Set objOutlook = Nothing
              DoCmd.ShowAllRecords
              DoCmd.SetOrderBy "[Deal ID] " & "DESC"
    End Sub

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I stopped reading when I saw this
    , "; " & Forms!ex.
    You want the value of what's in the form control, which is not a concatenation of anything. Even if it was, the ampersand would not be within the quotes.
    Sorry if that's not the solution to the problem.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    brownpride is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    10
    I don't follow what you are saying. I have the "; " so Outlook knows there are 2 separate e-mails. If I remove that, the e-mails will appear together and not separated. The e-mails pull from the form correctly.

    Also, if I use .display instead of .send then leave it open for about 10 seconds then outlook recognizes the e-mails (this is what I have to do currently). I want to make the process more automated. Is there a way to delay the command for about 10 seconds and maybe it won't error out.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sorry, I was in too much of a hurry. I wasn't commenting on the use of the semi colon, rather I saw the ampersand within the quotes when truly it isn't.
    What I do question now is after you create the application object and mail item object with the set statements, you should be assigning values to the mail item object properties, but if memory serves, you don't do this with a Set statement. Rather, if you're going to use a With block it's customary to put
    With objOutlookMsg
    .To =
    .CC =
    etc.
    End With
    I don't think you need to, nor should, Dim and set objects for another object's properties.

    and yes, there are ways to invoke a pause, but you shouldn't need one to assign one string to the To or CC or BCC properties. It may work while stepping through as you're giving it time to deal with setting another object inside of a With block that you Dim'd (objOutlookRecip).

    I use this in a standard module so I can call it from anywhere (like Pause 3 for 3 seconds)
    Code:
    Public Function Pause(intSecs As Integer)
    Dim Start As Variant
    Start = Timer
    Do While Timer < Start + intSecs
        DoEvents
    Loop
    End Function
    Hope that helps, because I've got to run off again.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    brownpride is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    10
    Oh wow! switching to .To fixed the problem. I just pulled the email template from a forum, didn't think about adjusting it. Thanks!

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    No problemo. Again, sorry I didn't read more thoroughly the first time.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 06-28-2015, 10:46 AM
  2. Replies: 3
    Last Post: 03-24-2015, 04:42 AM
  3. Replies: 2
    Last Post: 10-23-2012, 12:18 PM
  4. Replies: 3
    Last Post: 08-04-2011, 05:39 PM
  5. SQL query with an optional date field
    By StevenBee in forum Programming
    Replies: 1
    Last Post: 05-30-2011, 02:40 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums