Mailing a sheet without saving before
 

Hello,

I made a program in Excel that mails the Activesheet.
For each email I add the name of the repicient. So it is personalized. I
want to use a specific Outlook template.

To do this, I have to save my Excelsheet first. But that takes a lot of time.
So, I want to send my email without saving.

I use the following code.

Sub SendMyEmail()

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItemFromTemplate _
("C:\Templates\WMR.oft")

Set wb = Workbooks(Active_Excel_File)
With wb
.SaveAs "C:\Temp\" & MySubject & " " & MyDate & ".xls"

With OutMail
.To = email
.Subject = MySubject & " " & MyDate
.Attachments.Add wb.FullName
.Send
End With

.ChangeFileAccess xlReadOnly

Kill wb.FullName

Set OutMail = Nothing
Set OutApp = Nothing

End With

End Sub

Who can help me out?
Thanks a lot!

Willem

Mailing a sheet without saving before
 

That's not possible. Outlook can only attach a file programmatically if it has first been saved.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx


"WMR" wrote in message ...
Hello,

I made a program in Excel that mails the Activesheet.
For each email I add the name of the repicient. So it is personalized. I
want to use a specific Outlook template.

To do this, I have to save my Excelsheet first. But that takes a lot of time.
So, I want to send my email without saving.

I use the following code.

Sub SendMyEmail()

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItemFromTemplate _
("C:\Templates\WMR.oft")

Set wb = Workbooks(Active_Excel_File)
With wb
.SaveAs "C:\Temp\" & MySubject & " " & MyDate & ".xls"

With OutMail
.To = email
.Subject = MySubject & " " & MyDate
.Attachments.Add wb.FullName
.Send
End With

.ChangeFileAccess xlReadOnly

Kill wb.FullName

Set OutMail = Nothing
Set OutApp = Nothing

End With

End Sub

Who can help me out?
Thanks a lot!

Willem

Mailing a sheet without saving before
 

OK, but...

If I use this statement, it works well:
ActiveWorkbook.SendMail Recipients:=MyEmailAddresses, Subject:= MySubject

but I do not get C:\Templates\WMR.oft.

My Excelsheet is attached, but with a blank body.

Maybe you can help me further.

Tanks
Willem


"Sue Mosher [MVP-Outlook]" wrote:

That's not possible. Outlook can only attach a file programmatically if it has first been saved.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx


"WMR" wrote in message ...
Hello,

I made a program in Excel that mails the Activesheet.
For each email I add the name of the repicient. So it is personalized. I
want to use a specific Outlook template.

To do this, I have to save my Excelsheet first. But that takes a lot of time.
So, I want to send my email without saving.

I use the following code.

Sub SendMyEmail()

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItemFromTemplate _
("C:\Templates\WMR.oft")

Set wb = Workbooks(Active_Excel_File)
With wb
.SaveAs "C:\Temp\" & MySubject & " " & MyDate & ".xls"

With OutMail
.To = email
.Subject = MySubject & " " & MyDate
.Attachments.Add wb.FullName
.Send
End With

.ChangeFileAccess xlReadOnly

Kill wb.FullName

Set OutMail = Nothing
Set OutApp = Nothing

End With

End Sub

Who can help me out?
Thanks a lot!

Willem

Mailing a sheet without saving before
 

SendMail uses a different mechanism that doesn't invoke Outlook directly. If you want Outlook functionality, you have to play by Outlook's rules. In this case, that means saving the file before attaching it.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx


"WMR" wrote in message ...
OK, but...

If I use this statement, it works well:
ActiveWorkbook.SendMail Recipients:=MyEmailAddresses, Subject:= MySubject

but I do not get C:\Templates\WMR.oft.

My Excelsheet is attached, but with a blank body.

Maybe you can help me further.

Tanks
Willem


"Sue Mosher [MVP-Outlook]" wrote:

That's not possible. Outlook can only attach a file programmatically if it has first been saved.


"WMR" wrote in message ...
Hello,

I made a program in Excel that mails the Activesheet.
For each email I add the name of the repicient. So it is personalized. I
want to use a specific Outlook template.

To do this, I have to save my Excelsheet first. But that takes a lot of time.
So, I want to send my email without saving.

I use the following code.

Sub SendMyEmail()

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItemFromTemplate _
("C:\Templates\WMR.oft")

Set wb = Workbooks(Active_Excel_File)
With wb
.SaveAs "C:\Temp\" & MySubject & " " & MyDate & ".xls"

With OutMail
.To = email
.Subject = MySubject & " " & MyDate
.Attachments.Add wb.FullName
.Send
End With

.ChangeFileAccess xlReadOnly

Kill wb.FullName

Set OutMail = Nothing
Set OutApp = Nothing

End With

End Sub

Who can help me out?
Thanks a lot!

Willem