Excel - Outlook - Send As / From

User avatar
ErikJan
BronzeLounger
Posts: 1324
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Excel - Outlook - Send As / From

Post by ErikJan »

I have code in (Excel) VBA that builds and sends an email. I use this syntax...

Code: Select all

    Dim OLF As Object, olMailItem As Object
    Set OLF = GetObject("", "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(6)    'olFolderInbox
    Set olMailItem = OLF.items.Add    ' creates a new e-mail message
    With olMailItem
    ...
    End With
All of this works fine, just need to make one final adjustment. I have multiple accounts in Outlook (Desktop) and when I do this manually, I can click the "From" button and specify from which account I'd like to send the specific email.
I need to do the same in VBA above... I tried to use ".SendUsingAccount = "..." (where ... has the account email I'd like to be used to send the email).
Regretfully, that doesn't work (although there's no error).
Suggestions would be appreciated.

User avatar
ErikJan
BronzeLounger
Posts: 1324
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Excel - Outlook - Send As / From

Post by ErikJan »

No one with an idea here? If not, I'll post somewhere else but will share here if I found a solution.

User avatar
SpeakEasy
5StarLounger
Posts: 628
Joined: 27 Jun 2021, 10:46

Re: Excel - Outlook - Send As / From

Post by SpeakEasy »

.SendUsingAccount ought to work fine. Exact.y how are you setting it? it requires an Account object, e.g.

.SendUsingAccount = OLF.Application.Session.Accounts("name@mailbox.com")

User avatar
ErikJan
BronzeLounger
Posts: 1324
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Excel - Outlook - Send As / From

Post by ErikJan »

Thanks.

My OLF in my code connects to my default account. Also my OLF is already defined there as the Inbox "GetDefaultFolder(6)".

So I created another object like this:

Code: Select all

Set OLFAcc = GetObject("", "Outlook.Application").session.accounts
And then in the existing code called it like this (note that the .SendUsingAccount is connected to my OLF and not to ALF Acc):

Code: Select all

.SendUsingAccount = OLFAcc("name@mailbox.com")
(where I have used the right sender email address of course).

There's no errors, but the "From:" in the draft still lists my default account and not the one I'm trying to set.

User avatar
SpeakEasy
5StarLounger
Posts: 628
Joined: 27 Jun 2021, 10:46

Re: Excel - Outlook - Send As / From

Post by SpeakEasy »

OLF.Application.Session.Accounts is an instance of the the same object as GetObject("", "Outlook.Application").session.accounts, so you shouldn't need to got to the trouble of assigning OLFAcc. But if it makes it clearer to you what is going on, then fine.

That being said, can't see why it isn't working for you. As previously stated, t works absolutely fine here, Or it does if I early bind, rather than late bind. So try it with early binding to see if that helps.

This is the test code I am using (with the appropriate replacements for the recipient and account names):

Code: Select all

Public Sub Test()
    Dim OLF As Outlook.Folder
    Dim olMailItem As Outlook.MailItem
    Set OLF = GetObject("", "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(6)    'olFolderInbox
    Set olMailItem = OLF.items.Add
    With olMailItem
        .Recipients.Add "recipient@mailbox.com"
        .Subject = "test"
        .SendUsingAccount = OLF.Application.Session.Accounts("account@mailbox.com")
        .Display 'Send
        Stop
    End With
End Sub

User avatar
ErikJan
BronzeLounger
Posts: 1324
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Excel - Outlook - Send As / From

Post by ErikJan »

That works as long as I use my default account for "account@mailbox.com", as soon as I change that for any other account I have configured in Outlook I get an error message:
2024-05-31 19_18_59-Microsoft Visual Basic.png
You do not have the required permissions to view the files attached to this post.

User avatar
SpeakEasy
5StarLounger
Posts: 628
Joined: 27 Jun 2021, 10:46

Re: Excel - Outlook - Send As / From

Post by SpeakEasy »

Ok, so .Accounts can also take an Index number. So let's try

Code: Select all

.SendUsingAccount = OLF.Application.Session.Accounts(2)  '  Index of one normally points to default account, so use 2 for first alternative
instead of

Code: Select all

.SendUsingAccount = OLF.Application.Session.Accounts("account@mailbox.com")

User avatar
ErikJan
BronzeLounger
Posts: 1324
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Excel - Outlook - Send As / From

Post by ErikJan »

Thanks, it's solved by doing it like this. And I also understand now why:

Code: Select all

OLF.Application.session.accounts(1)
does not have to be the same as

Code: Select all

OLF.Application.session.accounts("account@mailbox.com")
The indexed one displays the NAME of the account (and that is what this call needs as it seems). However, the name does not have to be the same as the email-address associated with the account (and in my case it wasn't).
The email address could be 'account@maibox.com', but one can set the name as "John Doe email" for example.

The email address is shown like this:

Code: Select all

OLF.Application.session.accounts(i).SmtpAddress

User avatar
ErikJan
BronzeLounger
Posts: 1324
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Excel - Outlook - Send As / From

Post by ErikJan »

But with late binding I can't get it to work (not a big deal, but a pity)

User avatar
ErikJan
BronzeLounger
Posts: 1324
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Excel - Outlook - Send As / From

Post by ErikJan »

For mysterious reasons I'm having problems here again. Tried to clean-up my code to be able to share it here but now I get another error and I don't know why. So this will be the precursor to my real question.

This is my (reduced) code:

Code: Select all

Sub TestMail1(Recipient As String)
    Dim OLF As Object, olMailItem As Object 'Late Binding
    '
    Set OLF = GetObject("", "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(6)
    Set olMailItem = OLF.items.Add
    '
    With olMailItem
        .To = Recipient
        .Subject = "Test"
        .Save
        .Send
    End With
    '
    Set olMailItem = Nothing
    Set OLF = Nothing
End Sub
Nothing fancy as you can see. On the .Send line I get the following error however:
2024-10-10 22_08_49-Microsoft Visual Basic.png
The email does appear in the draft folder of my Outlook (classic) App however and it I manually open it there and click: "Send" it works...

I must be missing something very obvious...

FYI: "Recipient is a string with an email address (like something@something.com). I use MS365 and Win11 Pro (all latest updates)

PS. The ultimate goal is to change the "From:" email-address (see the earlier parts in this thread). I removed these parts for now.
So I'm fine if the syntax is changed as long as that capability is still possible.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 79521
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel - Outlook - Send As / From

Post by HansV »

1) Does it help if you change

Code: Select all

        .To = Recipient
to

Code: Select all

        .Recipients.Add Recipient
2) Does it help if you change the name of the variable Recipient to for example strRecipient or Recip (reason: Recipient is the name of an object in Outlook VBA)
Best wishes,
Hans

User avatar
ErikJan
BronzeLounger
Posts: 1324
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Excel - Outlook - Send As / From

Post by ErikJan »

No, I had that originally but gives the same error (the .To is merely a simplification but no change)

User avatar
HansV
Administrator
Posts: 79521
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel - Outlook - Send As / From

Post by HansV »

Are you absolutely sure that the value of Recipient is a valid email address? Have you tried with a different email address (for example on of your own)?
Best wishes,
Hans

User avatar
ErikJan
BronzeLounger
Posts: 1324
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Excel - Outlook - Send As / From

Post by ErikJan »

Yes it's valid. As I indicated, the email that is created by the code and saved as a draft by the ".Save" statement can be sent manually without problems.

User avatar
HansV
Administrator
Posts: 79521
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel - Outlook - Send As / From

Post by HansV »

I'm afraid I have no further suggestions, sorry.
Best wishes,
Hans

User avatar
ErikJan
BronzeLounger
Posts: 1324
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Excel - Outlook - Send As / From

Post by ErikJan »

Thanks for trying Hans :-)

User avatar
ErikJan
BronzeLounger
Posts: 1324
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Excel - Outlook - Send As / From

Post by ErikJan »

Out of curiosity, Hans, did you actually run my code yourself and did that reproduce the error message?
I need to eliminate possible other causes that could be local to just my system here...

User avatar
ErikJan
BronzeLounger
Posts: 1324
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Excel - Outlook - Send As / From

Post by ErikJan »

I also posted in another forum and there is was suggested to place:

.Recipients.ResolveAll

Before the ".Save" line.

That fixed my problem.

User avatar
SpeakEasy
5StarLounger
Posts: 628
Joined: 27 Jun 2021, 10:46

Re: Excel - Outlook - Send As / From

Post by SpeakEasy »

The code works fine here with a legitimate, resolvable email address in Recipient

Try the following minor alteration to your code:

Code: Select all

Sub TestMail1(Recipient As String)
    Dim OLF As Object, olMailItem As Object 'Late Binding
    Dim debugrecipient As Object
    '
    Set OLF = GetObject("", "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(6)
    Set olMailItem = OLF.items.Add
    '
    With olMailItem
        .To = Recipient
        
        .Recipients.ResolveAll
        For Each debugrecipient In .Recipients
            Debug.Print debugrecipient.Name, debugrecipient.Resolved
        Next
        
        .Subject = "Test"
        .Save
        .Send
    End With
    '
    Set olMailItem = Nothing
    Set OLF = Nothing
End Sub
It might also be worth pointing out that the Outlook Object Model cannot handle ambiguous names (e.g. "John Smith" will also match "John Smither") - it will fail to resolve ...
Last edited by SpeakEasy on 11 Oct 2024, 10:12, edited 1 time in total.

User avatar
ErikJan
BronzeLounger
Posts: 1324
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Excel - Outlook - Send As / From

Post by ErikJan »

Thank you. Yes, as I also just posted, the Resolve did it... ;-)