I am (successfully) using Excel VBA to send e-mails from Outlook. I would like to programatically select which of my Outlook Accounts to send from using Excel VBA. Does anyone have experience with this?
Thanks,
Win Sheffield
Printable View
I am (successfully) using Excel VBA to send e-mails from Outlook. I would like to programatically select which of my Outlook Accounts to send from using Excel VBA. Does anyone have experience with this?
Thanks,
Win Sheffield
Make a reference to Outlook library
This is the start for connecting to the contacts folder
[VBA]
Dim objApp As Application
Dim objNS As NameSpace
Dim objContacts As MAPIFolder
' get folder to search
Set objApp = CreateObject("Outlook.Application")
Set objNS = objApp.GetNamespace("MAPI")
Set objContacts = objNS.GetDefaultFolder(olFolderContacts)
[/VBA]
The problem is the security alert (in outlook 2002, don't know in outlook 2000) that will stop you with a msgbox telling you that another application is trying to get information from outlook
Bypassing Outlook Security Warning discussed and apparently resolved on this thread:
http://vbaexpress.com/forum/showthread.php?t=7847
seems this is the offending line Ale..
[VBA]
'Set objApp = CreateObject("Outlook.Application")
'should be
Set objApp = Application
[/VBA]
hope this helps
Hi Win,
Welcome to VBAX!
This is not easy and can only be done via the Outlook menus. How, exactly, are you setting up and sending your e-mail?
Thanks for your response Tony. I did pick up a solution that seemed to be trying to access the menus (see http://p2p.wrox.com/archive/vba_outlook/2002-10/8.asp), but I was not able to make it work. I am self-taught & MAPIFolder & GetNameSpace are so much mumbo jumbo to me. The key to it seems to be something called Inspector which I am not sure I have.Quote:
Originally Posted by TonyJollans
I have a subroutine which I adapted from somewhere (see below). I pull in data from Excel & from a form.
Thanks for any thoughts you have,
Win Sheffield
Sub SendEmail(Subject_Text, To_EMail, CC_EMail, BCC_EMail, Body_Text, Attachment_Name, Save_Send_Wait)
Dim OLF As Outlook.MAPIFolder, olMailItem As Outlook.MailItem
Dim ToContact As Outlook.Recipient
On Error GoTo Done
Set OLF = GetObject("", "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderIn box)
Set olMailItem = OLF.Items.Add 'creates a new e-mail message
With olMailItem
.Subject = Subject_Text 'message subject
Set ToContact = .Recipients.Add(To_EMail) 'add recipients
If CC_EMail <> "" Then
Set ToContact = .Recipients.Add(CC_EMail) 'add a recipient
ToContact.Type = olCC 'set latest recipient as CC
End If
If BCC_EMail <> "" Then
Set ToContact = .Recipients.Add(BCC_EMail) 'add a recipient
ToContact.Type = olBCC 'set latest recipient as CC
End If
.Body = Body_Text
If Attachment_Name <> "" Then
.Attachments.Add Attachment_Name, olByValue, , Attachment_Name 'attachment
End If
If Save_Send_Wait = "Save" Then
.Save
ElseIf Save_Send_Wait = "Send" Then
.Send
Else
.Display
End If
End With
Done:
On Error Resume Next
Set ToContact = Nothing
Set olMailItem = Nothing
Set OLF = Nothing
End Sub
Hi Win,
Yes, Outlook is sometimes wierd and wonderful! I am no expert so please take what I say as a guide, at best.
I believe that there are differences depending whether you have Outlook set up for Internet mail or on a corporate Exchange Server but I have no experience of Exchange.
Caveats over, ...
An Inspector is a sort of shell for an Item (in this case the Mail Item you are creating) which represents the edit window you would get if you clicked "New" in Outlook. In that window there is a button on the Standard Toolbar labeled "Accounts" and it is this which you need to access to change the Account - for security reasons there is no 'proper' access to accounts via VBA.
To gain access to the Inspector you use the GetInspector method of the MailItem. In your code you would add, firstly a declaration of an Inspector variable, and then - somewhere after creating the mail item and before sending it - a reference to it.[vba]Dim olMailInspector As Outlook.Inspector
:
:
Set olMailInspector = olMailItem.GetInspector[/vba]
You can then gain access to its Standard Toolbar, on which there is an "Accounts" button - a popup to select the sending account. To access this you again add a declaration at the top, and then set a reference from the inspector to the button (the ID 31224 uniquely identifies the right button)[vba]Dim olMailAccounts As Office.CommandBarPopup
:
:
Set olMailAccounts = olMailInspector.CommandBars.FindControl(ID:=31224)[/vba]
The first item on this is the account which will be used if you don't do anything. This is followed by a list of available accounts, each prefixed by a number or letter. You won't know what the number or letter is, so you need to examine each item to find the one you want, something like this:[vba]Dim olMailAccount As Office.CommandBarButton
Dim strAccountToUse As String
strAccountToUse = "Tony @ MVPS" ' Put your Account Name here
:
:
For Each olMailAccount In olMailAccounts.Controls
If Right$(olMailAccount.Caption, Len(strAccountToUse)) = strAccountToUse Then
olMailInspector.Activate ' It won't work without this - I don't know why
olMailAccount.Execute
End If
Next[/vba]
A point to watch out for: it seems as if the programmatic view of the Accounts is not exactly the same as the UI view so ...
(a) after you have run the code the current account will not appear (to your code) to have changed, and
(b) deleted accounts may appear in the list before the current account
You should find, after this, that the chosen account will be used when you do a Send command. If you have trouble integrating this into your code, please come back.