Issue Automating an Outlook Email from Access

[RMittelman]

I posted this question on MSOffice Forums, but haven't heard a peep there, so I'm assuming it wasn't the right place. So:

I have an Outlook class in my Access database, and it contains the following method

Public Sub CreateEMail(strETo As String, strECC As String, strESubject As String, strEBody As String, _                       ParamArray attachFiles() As Variant)

    Dim attachFile  As Variant
    
    Set olEmail = olApp.CreateItem(olMailItem)
    With olEmail
        
        .To = strETo
        .CC = strECC
        If Not IsNull(olAccount) Then
            .SendUsingAccount = olAccount
            .Sender = olAccount
        End If
        .Subject = strESubject
        .HTMLBody = strEBody & "<br>" & .HTMLBody
        
'        .body = Application.plainText(strEBody) & vbCrLf & .body
        
        For Each attachFile In attachFiles
            If attachFile <> "" Then .Attachments.Add attachFile
        Next
        
        If m_autoSend Then
            .Send
        Else
            .Display
        End If
        
    End With
 End Sub

Since I happen to have 2 different email addresses, I've previously set the variable olAccount to the preferred address. This variable is declared as Variant.

When the code is executed, I end up with an open Outlook email message ready to complete and send. This is good.

What's not good is the wrong email address (number 1 in my account list) is shown in the message, and I need to click the drop-down and choose the correct one (number 2 in my account list) in the message UI.

If I single step the code, the correct Outlook account is being used (at least in code variables), but when the outgoing message is displayed, it's the wrong account.

Any ideas to fix this? Thanks...

[Welshgasman]

This is what I used

        With objOutlookMsg
            .Subject = .Subject & " - " & intTransactions & " " & strType
            If intTransactions > 1 Then
                .Subject = .Subject & "s"
            End If
            
            ' Need to amend the footer depending on account being used intAccount = 2 = SSW, 3 is NPT
            If intAccount = 3 Then
                strFooter = Replace(strFooter, "Divisional Treasurer, Swansea South & West", "Temporary Divisional Treasurer, Neath & Port Talbot")
            End If
            
            ' Now add the footer
            .HTMLBody = .HTMLBody & "</table>" & strFooter
            '.Importance = olImportanceHigh  'High importance
            'Debug.Print strHeader
            'Debug.Print .htmlbody
            'Debug.Print strFooter
            ' Resolve each Recipient's name.
            For Each objOutlookRecip In .Recipients
                'Debug.Print objOutlookRecip.Name
                objOutlookRecip.Resolve
            Next
    
            ' Should we display the message before sending?
            .SendUsingAccount = objOutlook.Session.Accounts.Item(intAccount)
            If blnDisplayMsg Then
                .Display
            Else
                .Save
                .Send
            End If
        End With

If you do not want to hard code the number you can search for the relevant number using account name.
This is in Outlook, so you would need to modify if using in Access

Public Function ListEMailAccounts(AcctToUSe As String) As Integer
    Dim outApp As Object
    Dim i As Integer
    Dim AccNo As Integer
    Dim emailToSendTo As String
    
    Set outApp = CreateObject("Outlook.Application")
    'emailToSendTo = "xxxxxxx@gmail.com"                    'put required email address
    AccNo = 1
    'if smtp address=email we want to send to, acc no we are looking for is identified
    For i = 1 To outApp.Session.Accounts.Count
        'Uncomment the Debug.Print command to see all email addresses that belongs to you
'Debug.Print "Acc name: " & OutApp.Session.Accounts.Item(i) & " Acc number: " & i & " , email: " & OutApp.Session.Accounts.Item(i).SmtpAddress
        'If OutApp.Session.Accounts.Item(i).SmtpAddress = emailToSendTo Then
        If outApp.Session.Accounts.item(i).DisplayName = AcctToUSe Then


            AccNo = i
            Exit For
        End If
    Next i
    ListEMailAccounts = AccNo
    Set outApp = Nothing
End Function


Public Function ReadSignature(sigName As String) As String
    Dim oFSO, oTextStream, oSig As Object
    Dim appDataDir, sig, sigPath, fileName As String
    appDataDir = Environ("APPDATA") & "\Microsoft\Signatures"
    sigPath = appDataDir & "\" & sigName


    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oTextStream = oFSO.OpenTextFile(sigPath)
    sig = oTextStream.ReadAll
    ' fix relative references to images, etc. in sig
    ' by making them absolute paths, OL will find the image
    fileName = Replace(sigName, ".htm", "") & "_files/"
    sig = Replace(sig, fileName, appDataDir & "\" & fileName)
    ReadSignature = sig
End Function

Called with intAccount = ListEMailAccounts("My desired account")

[RMittelman]

Thanks for the reply Welshgasman. Unfortunately, I can't see how your code is much different than mine. I am also using the .SendUsingAccount property, but it's not causing my UI to show the proper account in the message being displayed. Single-stepping through my code seems to show the proper account being used, but it's not reflected in the UI.

[Minty]

I think the problem is you need to set the account I don't think it will not be null, so this code won't quite work;

If Not IsNull(olAccount) Then
            .SendUsingAccount = olAccount
            .Sender = olAccount
        End If

I use a variation which forces it to use a specific account;

 For Each oAccount In OApp.Session.Accounts
            If oAccount = "myemail@mydomain.com" Then
                .SendUsingAccount = oAccount
            End If
        Next

[RMittelman]

I think the problem is you need to set the account I don't think it will not be null, so this code won't quite work;

If Not IsNull(olAccount) Then
            .SendUsingAccount = olAccount
            .Sender = olAccount
        End If

I use a variation which forces it to use a specific account;

 For Each oAccount In OApp.Session.Accounts
            If oAccount = "myemail@mydomain.com" Then
                .SendUsingAccount = oAccount
            End If
        Next

Thanks for the suggestion, Minty. In the first snippet, I had already set the olAccount object when the calling code sets the FromEmail property, so it should never be null. I put the IF statement around there just in case the property had not been set.

In any case, I did switch to code like your second example, and it still does not work. Here is my Property Let for from email:

Private olAccount   As Object

Public Property Let FromEmail(vData As String)


    Dim temp    As String
    Dim acctID  As String
    Dim acct    As Object
    
    ' get actual email address
    If vData Like "* <*>" Then
        temp = Replace$(Mid$(vData, InStr(1, vData, "<") + 1), ">", "")
    Else
        temp = vData
    End If
    
    ' set the outlook account
    For Each acct In olAccounts
        If acct = temp Then
            Set olAccount = acct
            Exit For
        End If
    Next
    
    If olAccount Is Nothing Then Set olAccount = olAccounts(TempVars!SMTPId) ' This is a default "from" address
    If olAccount Is Nothing Then
        MsgBox "Could not select Outlook account.", vbExclamation
        m_fromEmail = ""
    Else
        m_fromEmail = vData
    End If


End Property

Here is the code to create the email:

Public Sub CreateEMail(strETo As String, strECC As String, strESubject As String, strEBody As String, _                       ParamArray attachFiles() As Variant)


    Dim attachFile  As Variant
    
    Set olEmail = olApp.CreateItem(olMailItem)
    With olEmail
        .To = strETo
        .CC = strECC
        .Subject = strESubject
        .HTMLBody = strEBody & "<br>" & .HTMLBody
        .Display
        
        If Not IsNull(olAccount) Then
            .SendUsingAccount = olAccount
            .Sender = olAccount
        End If
        
        For Each attachFile In attachFiles
            If attachFile <> "" Then .Attachments.Add attachFile
        Next
        
        If m_autoSend Then
            olEmail.Send
        Else
            olEmail.Display
        End If
    End With


End Sub

I tried moving the .Display instruction to above where I set the .SendUsingAccount, but that didn't help. Either way, when the email message is displayed it shows my first Outlook account instead of the desired one, which is my second Outlook account.

[Welshgasman]

So try it my way. What do you have to lose?

[Minty]

Stupid question(s) as I have used that code successfully more than once.

Do you have permissions on the account to sent ( I Assume yes but...)

Does you property work? Have you Debug.printed the result of it?
If it doesn't or returns the wrong result (e.g. Your account) then you are back to square one.

BTW - You'll never see your error message box, You're setting olAccount to something then displaying the message only if it's nothing.

As a trial Forget the property setting, and just hard code the lookup in your mail sending routine so it's really obvious what's happening and where.

[RMittelman]

Thanks Welshgasman. You're right, nothing to lose. Tried it, still didn't work. HOWEVER, please see next reply to Minty.

[RMittelman]

Stupid question(s) as I have used that code successfully more than once.

Do you have permissions on the account to sent ( I Assume yes but...)

Does you property work? Have you Debug.printed the result of it?
If it doesn't or returns the wrong result (e.g. Your account) then you are back to square one.

BTW - You'll never see your error message box, You're setting olAccount to something then displaying the message only if it's nothing.

As a trial Forget the property setting, and just hard code the lookup in your mail sending routine so it's really obvious what's happening and where.

Thanks for suggestions.
Yes, I have permissions.
Property does work, single-stepping through it seems to set variables properly.
Error message: Not so sure. There's been times I've set an object (though admittedly not while automating Outlook) and the object ended up being Nothing because the Set failed. You may be right, but it's just in case.

New progress:

I was trying to use this class late-bound in case somebody else wanted to use it and didn't want to set reference to Outlook.
Since it was unclear to me what Outlook objects "looked like" or how they should be created/set/invoked, I decided to early-bind my class. All of a sudden it worked properly with the exception of the fact that during creating an email I got fan error 13 (type mismatch) trying to supply the Sender property.

        If Not IsNull(olAccount) Then            Stop
            .SendUsingAccount = olAccount
'            .Sender = olAccount
        End If

Obviously my knowledge of the Outlook object model is a work in progress. Googling the object model, it looks like you don't need to supply both SendUsingAccount and Sender properties.

So, it works so far. Unfortunately, when I reworked it back to late-bound, it no longer works, and reverts back to the first sender account when I wanted the second one (even though everything looks good while single-stepping). So unless there's something I'm doing wrong with the late-binding, I guess I'm stuck with early-bound code.

Thanks again for the help (and thanks again Welshgasman).

[Welshgasman]

Sorry, I use early binding pretty much all of the time. That DB code that I posted was also solely for me in my volunteer work. No one else would eve ever use it.
I do not recall you ever mentioning late binding, not that I would have twigged, but might have googled the phrase below, as I have just done now.

Seems you are not alone. have a look at some of these links https://www.google.com/search?client=firefox-b-d&q=sendusing+account+late+binding+outlook

[Welshgasman]

Of course you could move your desired account to the top and see if that works?, however if others are using it, I would have thought you would need to look for that particular account as they might not have the same order as you?

[Minty]

I always use late bound code, as I don't know what version of outlook clients may be using.
The code I posted definitely works, but I know outlook is fussy about referring to variables not set within it's scope.

Being a bit blunt here - Stop checking to see if olAccount is null. It doesn't matter: you want to set it to something specific, so set it within the sending routine.
It's only four or five lines of code, I don't see much benefit to making it a property.
You could make the sending from address a global variable/property set from a table to save hard coding it.

[RMittelman]

I always use late bound code, as I don't know what version of outlook clients may be using.
The code I posted definitely works, but I know outlook is fussy about referring to variables not set within it's scope.

Being a bit blunt here - Stop checking to see if olAccount is null. It doesn't matter: you want to set it to something specific, so set it within the sending routine.
It's only four or five lines of code, I don't see much benefit to making it a property.
You could make the sending from address a global variable/property set from a table to save hard coding it.

Thanks for the replies.

I also use late binding for the same reason. Only tried early binding to solve the issue.
Agree with second point. The property was only to set the actual from address. I tacked on the account setting code as an after-thought.

The issue was solved as a result of using the google search link provided by Welshgasman (Thanks!). Turns out you can use late binding if you use the Set keyword when setting the SendUsingAccount property. But if you ARE using early binding, you can't use Set, it causes an error. So here is the final code.

#Const is_early_bound = False

#If is_early_bound Then
    Private olApp       As Outlook.Application
    Private olEmail     As Outlook.MailItem
    Private olAccounts  As Outlook.Accounts
#Else

    Private olApp       As Object
    Private olEmail     As Object
    Private olAccounts  As Object

    Public Enum OlItemType
        olMailItem = 0
        olAppointmentItem = 1
        olContactItem = 2
        olTaskItem = 3
        olJournalItem = 4
        olNoteItem = 5
        olPostItem = 6
        olDistributionListItem = 7
        olMobileItemSMS = 11
        olMobileItemMMS = 12
    End Enum

#End If


Private m_autoSend  As Boolean
Private m_fromEmail As String

Public Property Let AutoSend(vData As Boolean)
    m_autoSend = vData
End Property


Public Property Get FromEmail() As String
    FromEmail = m_fromEmail
End Property


Public Property Let FromEmail(vData As String)
    m_fromEmail = vData
End Property

Public Sub CreateEMail(strETo As String, strECC As String, strESubject As String, strEBody As String, _
                       ParamArray attachFiles() As Variant)


    Dim attachFile  As Variant
    
    #If is_early_bound Then
        Dim acct        As Outlook.account
    #Else
        Dim acct        As Object
    #End If


    Set olEmail = olApp.CreateItem(olMailItem)
    With olEmail
        
        .To = strETo
        .CC = strECC
        .Subject = strESubject
        .HTMLBody = strEBody & "<br>" & .HTMLBody
        
        For Each acct In olAccounts
            If acct = email_address(m_fromEmail) Then
                #If is_early_bound Then
                    .SendUsingAccount = acct
                #Else
                    Set .SendUsingAccount = acct
                #End If
                Exit For
            End If
        Next
        
'        .body = Application.plainText(strEBody) & vbCrLf & .body
        
        For Each attachFile In attachFiles
            If attachFile <> "" Then .Attachments.Add attachFile
        Next
        
        If m_autoSend Then
            olEmail.Send
        Else
            olEmail.Display
        End If
    End With


End Sub

Now it seems to work whether or not early binding is used.

Thanks both of you for your input!

[Minty]

Glad you have it solved, and thanks for posting the solution.
My only observation would be if using

.SendUsingAccount = acct

Always works where

Set .SendUsingAccount = acct

doesn't, I would always just use the first option.

[RMittelman]

Glad you have it solved, and thanks for posting the solution.
My only observation would be if using

.SendUsingAccount = acct

Always works where

Set .SendUsingAccount = acct

doesn't, I would always just use the first option.

We seem to have a disconnect here. I found an article which says the Set keyword is required if late binding, but causes an error if early binding. Since my code allows either method depending on the constant, I tried it both ways. Not using Set when late binding causes the property to be ignored, and the email message comes up with the wrong SendUsingAccount. Conversely, using Set with early binding indeed causes an error message. That's why I used the conditional compilation just in case somebody uses my class and they prefer early/late binding.