I use Ron de Bruin's Outlook object model (body) code for sending
Excel related matter via e-mail. All works great but is there a way to
direct the mail from a particular mail A/c that I have on my Outlook
(2003)?

I have 2 e-mail A/c's set up using address for Company A and address
for Company B, how could I change the code so that the mail is sent
from "address for Company A" or vice versa?

Thanks

hi Sean

See the tips page
http://www.rondebruin.nl/mail/tips2.htm

If you want to change the sender name and reply address add this code line

'The receiver can see the original mail address in the properties if he want
..SentOnBehalfOfName = """SenderName"" "






--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Sean" wrote in message ...
I use Ron de Bruin's Outlook object model (body) code for sending
Excel related matter via e-mail. All works great but is there a way to
direct the mail from a particular mail A/c that I have on my Outlook
(2003)?

I have 2 e-mail A/c's set up using address for Company A and address
for Company B, how could I change the code so that the mail is sent
from "address for Company A" or vice versa?

Thanks

Thanks Ron, is there no way other than having "Sent on Behalf of",
even though there is a valid outlook a/c that it could be routed
through (apart from the default a/c)?

Yes It is possible to change accounts with VBA
I see if I can dig up a code example for you so you can test it.

I have only one account in Outlook (I am a OE/windows Mail fan)

I see if I have time this evening

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Sean" wrote in message ...
Thanks Ron, is there no way other than having "Sent on Behalf of",
even though there is a valid outlook a/c that it could be routed
through (apart from the default a/c)?

Thanks Ron, the .sendonbehalfofname works great except I just wish to
see the other A/c as the "From name & address" rather than the
default.

I tried various suggestions you have made on past post such as
.SenderName = """ABC Company"""
.SenderEmailAddress = "

But they just send from the name on the default a/c

If the above were to work on a valid outlook a/c, is it the "Log in"
information which controls where it is sent from and hence must be
included within the VB?

There was a thread about changing the sending account in one of the
Outlook newsgroups (I believe microsoft.public.outlook.program_vba),
I'll try to find it and send you a link, but I believe the response
was that it isn't possible without CDO/Redemption.


--JP

On Jan 23, 11:24*am, Sean wrote:
Thanks Ron, the .sendonbehalfofname works great except I just wish to
see the other A/c as the "From name & address" rather than the
default.

I tried various suggestions you have made on past post such as
* * * * * * .SenderName = """ABC Company"""
* * * * * * .SenderEmailAddress = "

But they just send from the name on the default a/c

If the above were to work on a valid outlook a/c, is it the "Log in"
information which controls where it is sent from and hence must be
included within the VB?

Found it --

http://tinyurl.com/yv7wgs


HTH,
JP


On Jan 23, 11:24*am, Sean wrote:
Thanks Ron, the .sendonbehalfofname works great except I just wish to
see the other A/c as the "From name & address" rather than the
default.

I tried various suggestions you have made on past post such as
* * * * * * .SenderName = """ABC Company"""
* * * * * * .SenderEmailAddress = "

But they just send from the name on the default a/c

If the above were to work on a valid outlook a/c, is it the "Log in"
information which controls where it is sent from and hence must be
included within the VB?

Thanks for the link

I have heard of redemption but not quite sure how to apply it. I'm
assuming I would have to change my (Ron de Bruin's) entire code, which
I'd prefer not to do as its quite involved

Redemption only requires a few changes to your code, check out the
link in the other thread, there is some
sample code for how to set the sending account.

http://www.dimastr.com/redemption/rd...htm#properties


HTH,
JP

On Jan 23, 12:11*pm, Sean wrote:
Thanks for the link

I have heard of redemption but not quite sure how to apply it. I'm
assuming I would have to change my (Ron de Bruin's) entire code, which
I'd prefer not to do as its quite involved

Thanks JP, off site at the moment so ca't test, but is it the case I
could leave code as is and add the following, it may well work?

set Session = CreateObject("Redemption.RDOSession")
Session.Logon
set Drafts = Session.GetDefaultFolder(olFolderDrafts)
set Msg = Drafts.Items.Add
set Account = Session.Accounts("My ISP account")
Msg.Account = Account

<my code here

Msg.Save
Msg.Send

I have other code in my original hat sets, the "To"; "Subject";
"Message Body" etc so don't wish to touch them

I'm not that familiar with Redemption, you may want to post your code
in that newsgroup (microsoft.public.outlook.program_vba) and mention
that you want to use Redemption to set the sending account when
automating Outlook from Excel. Also you definitely should post more of
your code.


HTH,
JP

On Jan 23, 1:59*pm, Sean wrote:
Thanks JP, off site at the moment so ca't test, but is it the case I
could leave code as is and add the following, it may well work?

set Session = CreateObject("Redemption.RDOSession")
Session.Logon
set Drafts = Session.GetDefaultFolder(olFolderDrafts)
set Msg = Drafts.Items.Add
set Account = Session.Accounts("My ISP account")
Msg.Account = Account

<my code here

Msg.Save
Msg.Send

I have other code in my original hat sets, the "To"; "Subject";
"Message Body" etc so don't wish to touch them

Hi Sean

The Outlook guys tell me this

Use Redemption or read this thread
http://www.vbaexpress.com/forum/show...t=8076&p=64353

In 2007 you can use the new mailItem.SendUsingAccount property



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Sean" wrote in message ...
Thanks Ron, the .sendonbehalfofname works great except I just wish to
see the other A/c as the "From name & address" rather than the
default.

I tried various suggestions you have made on past post such as
.SenderName = """ABC Company"""
.SenderEmailAddress = "

But they just send from the name on the default a/c

If the above were to work on a valid outlook a/c, is it the "Log in"
information which controls where it is sent from and hence must be
included within the VB?

I've installed Redemption but I'm lost as to how I tweak my code. My
original working code is below. It appears
that I need to place the following code somewhere in my code, but I
get a "property is read only" on the first line just below

Set Session = CreateObject("Redemption.RDOSession")
Session.Logon
Set Drafts = Session.GetDefaultFolder(olFolderDrafts)
Set Msg = Drafts.Items.Add
Set Account = Session.Accounts("123 Reporting")
Msg.Account = Account
Msg.Send

On the site http://www.dimastr.com/redemption/ FAQ # 14 it seems to
detail exactly what I want, but I don't know how to integrate it in my
code, the code they suggest is below

set sItem = CreateObject("Redemption.SafeMailItem")
sItem.Item = MailItem
tag = sItem.GetIDsFromNames("{00020386-0000-0000-C000-000000000046}",
"From")
tag = tag or &H1E 'the type is PT_STRING8
sItem.Fields(Tag) = "Someone "
sItem.Subject = sItem.Subject 'to trick Outlook into thinking that
something has changed
sItem.Save

Sub Mail_From_Excel()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet


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


Set Sourcewb = ActiveWorkbook


Sourcewb.Sheets(Array("Mail", "E-YTD")).Copy
Set Destwb = ActiveWorkbook


'Determine the Excel version and file extension/format
With Destwb
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
'We exit the sub when your answer is NO in the security
dialog that you only
'see when you copy a sheet from a xlsm file with macro's
disabled.
If Sourcewb.Name = .Name Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your answer is NO in the security dialog"
Exit Sub
Else
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End If
End With


TempFilePath = Environ$("temp") & "\"
TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now,
"dd-
mmm-yy h-mm")


ActiveWindow.TabRatio = 0.908


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


For Each cell In ThisWorkbook.Sheets("Mail") _
.Columns("BA").Cells.SpecialCells(xlCellTypeConsta nts)
If cell.Value Like "?*@?*.?*" Then
strto = strto & cell.Value & ";"
End If
Next
strto = Left(strto, Len(strto) - 1)


With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum


On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = strto
.Subject = ThisWorkbook.Sheets("Mail").Range("A1").Value
.Body = ""
.Attachments.Add Destwb.FullName
.ReadReceiptRequested = True
.Importance = 1
.DeferredDeliveryTime =
ThisWorkbook.Sheets("Mail").Range("B1").Value
.Send
End With
On Error GoTo 0
.Close savechanges:=False
End With


Kill TempFilePath & TempFileName & FileExtStr


Set OutMail = Nothing
Set OutApp = Nothing


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

I never used Redemption Sean so I can't help you.
I have no time on this moment to test it for you, sorry

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Sean" wrote in message ...
I've installed Redemption but I'm lost as to how I tweak my code. My
original working code is below. It appears
that I need to place the following code somewhere in my code, but I
get a "property is read only" on the first line just below

Set Session = CreateObject("Redemption.RDOSession")
Session.Logon
Set Drafts = Session.GetDefaultFolder(olFolderDrafts)
Set Msg = Drafts.Items.Add
Set Account = Session.Accounts("123 Reporting")
Msg.Account = Account
Msg.Send

On the site http://www.dimastr.com/redemption/ FAQ # 14 it seems to
detail exactly what I want, but I don't know how to integrate it in my
code, the code they suggest is below

set sItem = CreateObject("Redemption.SafeMailItem")
sItem.Item = MailItem
tag = sItem.GetIDsFromNames("{00020386-0000-0000-C000-000000000046}",
"From")
tag = tag or &H1E 'the type is PT_STRING8
sItem.Fields(Tag) = "Someone "
sItem.Subject = sItem.Subject 'to trick Outlook into thinking that
something has changed
sItem.Save

Sub Mail_From_Excel()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet


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


Set Sourcewb = ActiveWorkbook


Sourcewb.Sheets(Array("Mail", "E-YTD")).Copy
Set Destwb = ActiveWorkbook


'Determine the Excel version and file extension/format
With Destwb
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
'We exit the sub when your answer is NO in the security
dialog that you only
'see when you copy a sheet from a xlsm file with macro's
disabled.
If Sourcewb.Name = .Name Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your answer is NO in the security dialog"
Exit Sub
Else
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End If
End With


TempFilePath = Environ$("temp") & "\"
TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now,
"dd-
mmm-yy h-mm")


ActiveWindow.TabRatio = 0.908


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


For Each cell In ThisWorkbook.Sheets("Mail") _
.Columns("BA").Cells.SpecialCells(xlCellTypeConsta nts)
If cell.Value Like "?*@?*.?*" Then
strto = strto & cell.Value & ";"
End If
Next
strto = Left(strto, Len(strto) - 1)


With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum


On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = strto
.Subject = ThisWorkbook.Sheets("Mail").Range("A1").Value
.Body = ""
.Attachments.Add Destwb.FullName
.ReadReceiptRequested = True
.Importance = 1
.DeferredDeliveryTime =
ThisWorkbook.Sheets("Mail").Range("B1").Value
.Send
End With
On Error GoTo 0
.Close savechanges:=False
End With


Kill TempFilePath & TempFileName & FileExtStr


Set OutMail = Nothing
Set OutApp = Nothing


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