.sendusingaccount ? for OL 2007

  • Thread starter Thread starter Bruce
  • Start date Start date
B

Bruce

I have tried to research this and I can not find much.

Here is the code I have. It still sends from the defualt account. I have 21
email accounts and this is to be sending from the 21st account. I in the
below code, I have tried using from the 3r account.

Sub Mail_Selection_Range_Outlook_Body()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set rng = Nothing
On Error Resume Next
'Only the visible cells in the selection
'Set rng = Selection.SpecialCells(xlCellTypeVisible)
'You can also use a range if you want
Set rng =
Sheets("Invoice").Range("B7:I47").SpecialCells(xlCellTypeVisible)
emailname = Range("M21").Value
bbcname = "(e-mail address removed)"
MsgBox emailname


On Error GoTo 0

If rng Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub
End If

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = emailname
.CC = ""
.BCC = bbcname
.Subject = "Invoice for - " & Range("L6").Value & " - " &
Application.Text(Range("L4").Value, "mmm-dd-yyyy")
.HTMLBody = RangetoHTML(rng)
.Send 'or use .Display
.SendUsingAccount = OutApp.Accounts(3)

End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub
 
Hello Sue,

I have tried moving it up in the code block, but I still can not change the
account.
What am I missing?

I know you have told others that it needs to be an object, but how do I do
that? Is it going to be an object the way I have my code written?
I KNOW I am close, but....

Thanks
Bruce

You need to set SendUsingAccount before you send the item.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/article.aspx?id=54
 
OutApp.Accounts(3) returns an Account object.

I would comment out the On Error Resume Next statement to make sure an error isn't being overlooked.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/article.aspx?id=54
 
'K
I commented out the on error, and now I am getting an error.
run-time error '438':
Object doesn't support this program or method.

I DO have Outlook 12 selected in tool>references
.SendUsingAccount = OutApp.Accounts(3)

is the line that throws the error.

Here is how the code block is right now:

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

' On Error Resume Next
With OutMail
.To = emailname
.CC = ""
.BCC = bbcname
.SendUsingAccount = OutApp.Accounts(3)

.Subject = "Invoice for - " & Range("L6").Value & " - " &
Application.Text(Range("L4").Value, "mmm-dd-yyyy")
.HTMLBody = RangetoHTML(rng)
.Send 'or use .Display

End With

What am I missing?
I HAVE gotten the following code working, and msgbox oAccount shows me the
account name for each loop.

Dim oAccount As Outlook.Account

' For Each oAccount In Application.Session.Accounts
' 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
' Next



Thanks
Bruce

OutApp.Accounts(3) returns an Account object.
I would comment out the On Error Resume Next statement to make sure an error
isn't being overlooked.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/article.aspx?id=54
 
The object browser is our friend. It's Namespace.Acccounts, not Application.Accounts. Good way to remember: Anything that is dependent on mail profile settings derives from the Namespace object.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/article.aspx?id=54
 
Sue,

Thank you for your help. I made the change to
..SendUsingAccount = namespace.Accounts(3)

but now I am getting RT error 424
Object required

Againg this is being thrown on the line:
..SendUsingAccount = Namespace.Accounts(3)

Also, just to be clear (I did not put it in my first post) this is in EXCEL!

Thanks again
Bruce





The object browser is our friend. It's Namespace.Acccounts, not
Application.Accounts. Good way to remember: Anything that is dependent on
mail profile settings derives from the Namespace object.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/article.aspx?id=54
 
Namespace is the name of the object, in other words the class, that you need to use, not the name of an object variable in your application. You need to instantiate a Namespace object using the Outlook object model's Application.GetNamespace method.
--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/article.aspx?id=54
 
Sue,
I hate being a pain...
I am not as strong of a programmer as you give me credit for:.)
Could you please give me a little code sample that would allow me to send a
message to an reciepiant, and use a different account than the defualt?
Here is pretty much what I can see that should shoot the email out.

Sub Mail_Selection_Range_Outlook_Body1()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
emailname = "(e-mail address removed)"
bbcname = "(e-mail address removed)"
MsgBox "Invoice is being sent to : " & emailname
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
Namespace = Outlook.Application.GetNamespace("MAPI")
With OutMail
.To = emailname
.CC = ""
.BCC = bbcname
.SendUsingAccount = Namespace.Accounts(3) 'how do I select a
different account?
.Subject = "Invoice for - "
.HTMLBody = "this is a test"
.Send 'or use .Display

End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Thank you very much for your continued help!
Bruce



Namespace is the name of the object, in other words the class, that you need
to use, not the name of an object variable in your application. You need to
instantiate a Namespace object using the Outlook object model's
Application.GetNamespace method.
--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/article.aspx?id=54
 
It looks like you're still not grapsing the issues of instantiating an object variable and using object methods. Hate to say it but this is VBA 101, and if you can't get past this, you're not going to get anywhere. Let's try again:

Accounts are an object property of the Namespace object.

The Namespace is an object property of the Outlook.Application object from which all Outlook objects need to be derived.

Therefore, to get an Account, you need an Outlook.Application object to start with. That you already have:

Set OutApp = CreateObject("Outlook.Application")

Next you need a Namespace object. If you're automating Outlook from another application, you need to use the GetNamespace method. That looks like this:

Set OutNS = OutApp.GetNamespace("MAPI")
OutNS.Logon

If Outlook is already running, that will be enough. Otherwise, your Logon statement may need to specify a mail profile or give the user a chance to choose one. Read the Help topic on Namespace.Logon for details.

To get an Account object to use in setting the sending account, use its name to retrive it from the Accounts collection of the Namespace object that we just instantiated:

Set OutAcct = OutNS.Accounts("name_of_the_account")

Make sense? Can you take it from there?
--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/article.aspx?id=54
 
Here is what I have.
this works in Excel if I have all that is assigning the oAccount commented
out, and the for loop active., It sends an email from every account. - not
what I want to do. When I uncomment out any of the lines that assign
oaccount or oacct, I get the errors listed.
I have gotten code to work fine in Outlook. and am just trying to get it to
work in Excel. It looks like it should from your previous comments.
Thanks!



Sub A_SendUsingAccount()
Dim oAccount As Outlook.Account
Set OutApp = CreateObject("Outlook.Application")
Set OutNS = OutApp.GetNamespace("MAPI")
OutNS.Logon
Set OutMail = OutApp.CreateItem(0)

'Set outAccount = 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 ([email protected])
oMail.Recipients.ResolveAll
oMail.SendUsingAccount = oAcct
oMail.Send
End If
'Next
End Sub

The following works fine in OL2007

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)"

Set OutApp = CreateObject("Outlook.Application")
Set OutNS = OutApp.GetNamespace("MAPI")
OutNS.Logon
Set OutMail = OutApp.CreateItem(0)
Set oAccount = Application.Session.Accounts(21)
'SAccount = Accounts(oAccount)
oaccountcnt = Application.Session.Accounts.count

MsgBox oAccount 'let me know what it found. returns the account I want to
email from

MsgBox oaccountcnt 'let me know how many accounts it finds. It does find 21

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


It looks like you're still not grapsing the issues of instantiating an
object variable and using object methods. Hate to say it but this is VBA
101, and if you can't get past this, you're not going to get anywhere. Let's
try again:

Accounts are an object property of the Namespace object.

The Namespace is an object property of the Outlook.Application object from
which all Outlook objects need to be derived.

Therefore, to get an Account, you need an Outlook.Application object to
start with. That you already have:

Set OutApp = CreateObject("Outlook.Application")

Next you need a Namespace object. If you're automating Outlook from another
application, you need to use the GetNamespace method. That looks like this:

Set OutNS = OutApp.GetNamespace("MAPI")
OutNS.Logon

If Outlook is already running, that will be enough. Otherwise, your Logon
statement may need to specify a mail profile or give the user a chance to
choose one. Read the Help topic on Namespace.Logon for details.

To get an Account object to use in setting the sending account, use its name
to retrive it from the Accounts collection of the Namespace object that we
just instantiated:

Set OutAcct = OutNS.Accounts("name_of_the_account")

Make sense? Can you take it from there?
--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/article.aspx?id=54
 
Is Outlook running when you test this? Either of these statements should be valid if the Namespace object is logged on to an Outlook session:

Set outAccount = OutNS.Accounts.Item("(e-mail address removed)")

where "(e-mail address removed)" is the display name of the account, or

Set oAccount = OutNS.Accounts.Item(21)

if there actually are 21 separate accounts. Another approach is to enumerate the accounts with a For Each ... Next loop until you find the one with the right Account.DisplayName value.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/article.aspx?id=54

Bruce said:
Here is what I have.
this works in Excel if I have all that is assigning the oAccount commented
out, and the for loop active., It sends an email from every account. - not
what I want to do. When I uncomment out any of the lines that assign
oaccount or oacct, I get the errors listed.
I have gotten code to work fine in Outlook. and am just trying to get it to
work in Excel. It looks like it should from your previous comments.
Thanks!



Sub A_SendUsingAccount()
Dim oAccount As Outlook.Account
Set OutApp = CreateObject("Outlook.Application")
Set OutNS = OutApp.GetNamespace("MAPI")
OutNS.Logon
Set OutMail = OutApp.CreateItem(0)

'Set outAccount = 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 ([email protected])
oMail.Recipients.ResolveAll
oMail.SendUsingAccount = oAcct
oMail.Send
End If
'Next
End Sub

The following works fine in OL2007

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)"

Set OutApp = CreateObject("Outlook.Application")
Set OutNS = OutApp.GetNamespace("MAPI")
OutNS.Logon
Set OutMail = OutApp.CreateItem(0)
Set oAccount = Application.Session.Accounts(21)
'SAccount = Accounts(oAccount)
oaccountcnt = Application.Session.Accounts.count

MsgBox oAccount 'let me know what it found. returns the account I want to
email from

MsgBox oaccountcnt 'let me know how many accounts it finds. It does find 21

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
Click to expand...
 
Outlook IS running...
Is Outlook running when you test this? Either of these statements should be
valid if the Namespace object is logged on to an Outlook session:

Set outAccount = OutNS.Accounts.Item("(e-mail address removed)")

where "(e-mail address removed)" is the display name of the account, or

Set oAccount = OutNS.Accounts.Item(21)

if there actually are 21 separate accounts. Another approach is to enumerate
the accounts with a For Each ... Next loop until you find the one with the
right Account.DisplayName value.
I should be able to do this as for next loop DID work. A little slower and
not a clean, but it should work.

Thanks
Bruce


--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/article.aspx?id=54

Bruce said:
Here is what I have.
this works in Excel if I have all that is assigning the oAccount commented
out, and the for loop active., It sends an email from every account. - not
what I want to do. When I uncomment out any of the lines that assign
oaccount or oacct, I get the errors listed.
I have gotten code to work fine in Outlook. and am just trying to get it
to
work in Excel. It looks like it should from your previous comments.
Thanks!



Sub A_SendUsingAccount()
Dim oAccount As Outlook.Account
Set OutApp = CreateObject("Outlook.Application")
Set OutNS = OutApp.GetNamespace("MAPI")
OutNS.Logon
Set OutMail = OutApp.CreateItem(0)

'Set outAccount = 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 ([email protected])
oMail.Recipients.ResolveAll
oMail.SendUsingAccount = oAcct
oMail.Send
End If
'Next
End Sub

The following works fine in OL2007

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)"

Set OutApp = CreateObject("Outlook.Application")
Set OutNS = OutApp.GetNamespace("MAPI")
OutNS.Logon
Set OutMail = OutApp.CreateItem(0)
Set oAccount = Application.Session.Accounts(21)
'SAccount = Accounts(oAccount)
oaccountcnt = Application.Session.Accounts.count

MsgBox oAccount 'let me know what it found. returns the account I want to
email from

MsgBox oaccountcnt 'let me know how many accounts it finds. It does find
21

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
Click to expand...
 
Back
Top