Help converting a macro from Outlook2007 to Excel2007

[Bruce]

I got the following macro to finally work in Outlook. I am trying to send
from 1 specific account. I am trying to write a module that will send
invoices from excel. I have that portion working from the DEFUALT account,
but I need to send this from 1 specific business email account. It seems
that there is VERY little on how to do this on the net. Any ways, the
following code DOES work in Outlook 2007. How would I make the changes to
get it to work in Excel?
I keep getting errors on the
Set oAccount = Application.Session.Accounts(21)
I have tried making what I think would be the correct changes, but to no
avail.

Thanks
Bruce


Sub Amailtest()
Dim OutApp As Object
Dim OutNS As Object
Dim OutAcct As Object
Dim oAccount As Outlook.Account
Dim OutMail As Object
Dim EmailName As String
EmailName = "(e-mail address removed)"

'Get valid email account that is wanted - in this case it is the 21st
account
Set OutApp = CreateObject("Outlook.Application")
Set OutNS = OutApp.GetNamespace("MAPI")
OutNS.Logon
Set OutMail = OutApp.CreateItem(0)
Set oAccount = Application.Session.Accounts(21)
oaccountcnt = Application.Session.Accounts.count
MsgBox oAccount
MsgBox oaccountcnt


If oAccount.AccountType = olPop3 Then
Dim oMail As Outlook.MailItem
Set oMail = Application.CreateItem(olMailItem)
oMail.Subject = "Sent using POP3 Account"
oMail.Recipients.Add EmailName
oMail.Recipients.ResolveAll
oMail.SendUsingAccount = oAccount
oMail.Send
MsgBox oAccount
End If

End Sub

 

[Keith74]

Hi

Don't really know outlook VBA but Set oAccount =
Application.Session.Accounts(21) would still think its running in
excel try Set oAccount = OutApp.Session.Accounts(21) instead. And
make sure you have the reference to the MS Outlook object library
selected.

hth

Keith

 

[Bruce]

Here are different combos that I can think of that should set the account
and then the errors I get with them.


Sub A_SendUsingAccount()
Dim oAccount As Outlook.Account
Dim outacct As Outlook.Account
Set OutApp = CreateObject("Outlook.Application")
Set OutNS = OutApp.GetNamespace("MAPI")
OutNS.Logon
Set OutMail = OutApp.CreateItem(0)
' Set outacct = OutNS.Accounts("(e-mail address removed)") '" Run-time
error '450': Wrong number of arguments or invalid property assignment"
' Set oAcct = OutNS.Session.Accounts(21) '" Run-time error '450': Wrong
number of arguments or invalid property assignment"
'Set oAccount = OutNS.Accounts(21) '" Run-time error '450': Wrong number
of arguments or invalid property assignment"
'Set oAcct = OutNS.Session.Accounts(21)'" Run-time error '450': Wrong
number of arguments or invalid property assignment"
' oAccount = OutApp.Session.Accounts(21) '" Run-time error '450': Wrong
number of arguments or invalid property assignment"
' Set oAccount = OutApp.Session.Accounts(21) '" Run-time error '450':
Wrong number of arguments or invalid property assignment"

' oaccountcnt = Application.Session.Accounts.Count

' MsgBox oAccount

' MsgBox oaccountcnt

' For Each oAccount In OutApp.Session.Accounts
If oAccount.AccountType = olPop3 Then
Dim oMail As Outlook.MailItem
Set oMail = OutApp.CreateItem(olMailItem)
oMail.Subject = "Sent using POP3 Account"
oMail.Recipients.Add ("(e-mail address removed)")
oMail.Recipients.ResolveAll
oMail.SendUsingAccount = oAcct
oMail.Send
End If
'Next
End Sub