Excel macro to create Outlook e-mail
 

I realize that this is a repeat of a question from yesterday - but no one
answered it.

The macro below will safe a draft when outlook is open, but will not when
outlook is closed. How can I make the change to save a draft - even if
outlook is closed?

This is my poor attempt in creating a Macro - if you see improvements that I
can make please let me know.....


Option Explicit

Sub SendFileToEmail()
Dim objOutlook As Object
Dim objOutlookMsg As Object
Dim strTo As String, strCC As String, strBody As String
Dim iRow As Long, inrec As String
Set objOutlook = CreateObject("Outlook.Application")
' Set objOutlookMsg = objOutlook.CreateItem(0)
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
Dim File1, File2 As String
Dim myCell

iRow = 0
strTo = ""
With shtEmail.Range("EmailToList")
Do
strTo = strTo & .Offset(iRow, 0).Value & ";"
iRow = iRow + 1
Loop Until .Offset(iRow, 0) = ""
End With

' iRow = 0
' strCC = ""
' With shtEmail.Range("CCToList")
' Do
' strCC = strCC & .Offset(iRow, 0).Value & ";"
' iRow = iRow + 1
' Loop Until .Offset(iRow, 0) = ""
' End With

For Each myCell In shtInput.Range("LifeSummary")
strBody = strBody & myCell.Value & vbCrLf
Next


With objOutlookMsg
'.SentonBehalfofName = "
..To = strTo
' .Cc = strCC
'.bcc = "
..Subject = "Today's information"
'.BodyFormat = olFormatPlain
..BodyFormat = olFormatHTML
..HTMLBody = "<html<pre" & strBody & "</pre</html"
'.Attachments.Add ("myfilenamewithfullpath.xls")
' .Send 'Let´s go!
..Save ' stick it in drafts
End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

Excel macro to create Outlook e-mail
 

Brad,
See GetObject in Help.
You can use it to test if Outlook is open, if not open it.

There may be a way to save a draft to a closed Outlook using MAPI.
It's really an Outlook problem. You should try the Outlook groups.

John


"Brad" wrote in message
...
I realize that this is a repeat of a question from yesterday - but no one
answered it.

The macro below will safe a draft when outlook is open, but will not when
outlook is closed. How can I make the change to save a draft - even if
outlook is closed?

This is my poor attempt in creating a Macro - if you see improvements that
I
can make please let me know.....


Option Explicit

Sub SendFileToEmail()
Dim objOutlook As Object
Dim objOutlookMsg As Object
Dim strTo As String, strCC As String, strBody As String
Dim iRow As Long, inrec As String
Set objOutlook = CreateObject("Outlook.Application")
' Set objOutlookMsg = objOutlook.CreateItem(0)
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
Dim File1, File2 As String
Dim myCell

iRow = 0
strTo = ""
With shtEmail.Range("EmailToList")
Do
strTo = strTo & .Offset(iRow, 0).Value & ";"
iRow = iRow + 1
Loop Until .Offset(iRow, 0) = ""
End With

' iRow = 0
' strCC = ""
' With shtEmail.Range("CCToList")
' Do
' strCC = strCC & .Offset(iRow, 0).Value & ";"
' iRow = iRow + 1
' Loop Until .Offset(iRow, 0) = ""
' End With

For Each myCell In shtInput.Range("LifeSummary")
strBody = strBody & myCell.Value & vbCrLf
Next


With objOutlookMsg
'.SentonBehalfofName = "
.To = strTo
' .Cc = strCC
'.bcc = "
.Subject = "Today's information"
'.BodyFormat = olFormatPlain
.BodyFormat = olFormatHTML
.HTMLBody = "<html<pre" & strBody & "</pre</html"
'.Attachments.Add ("myfilenamewithfullpath.xls")
' .Send 'Let´s go!
.Save ' stick it in drafts
End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

Excel macro to create Outlook e-mail
 

Thank you!
--
Wag more, bark less


"jaf" wrote:

Brad,
See GetObject in Help.
You can use it to test if Outlook is open, if not open it.

There may be a way to save a draft to a closed Outlook using MAPI.
It's really an Outlook problem. You should try the Outlook groups.

John


"Brad" wrote in message
...
I realize that this is a repeat of a question from yesterday - but no one
answered it.

The macro below will safe a draft when outlook is open, but will not when
outlook is closed. How can I make the change to save a draft - even if
outlook is closed?

This is my poor attempt in creating a Macro - if you see improvements that
I
can make please let me know.....


Option Explicit

Sub SendFileToEmail()
Dim objOutlook As Object
Dim objOutlookMsg As Object
Dim strTo As String, strCC As String, strBody As String
Dim iRow As Long, inrec As String
Set objOutlook = CreateObject("Outlook.Application")
' Set objOutlookMsg = objOutlook.CreateItem(0)
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
Dim File1, File2 As String
Dim myCell

iRow = 0
strTo = ""
With shtEmail.Range("EmailToList")
Do
strTo = strTo & .Offset(iRow, 0).Value & ";"
iRow = iRow + 1
Loop Until .Offset(iRow, 0) = ""
End With

' iRow = 0
' strCC = ""
' With shtEmail.Range("CCToList")
' Do
' strCC = strCC & .Offset(iRow, 0).Value & ";"
' iRow = iRow + 1
' Loop Until .Offset(iRow, 0) = ""
' End With

For Each myCell In shtInput.Range("LifeSummary")
strBody = strBody & myCell.Value & vbCrLf
Next


With objOutlookMsg
'.SentonBehalfofName = "
.To = strTo
' .Cc = strCC
'.bcc = "
.Subject = "Today's information"
'.BodyFormat = olFormatPlain
.BodyFormat = olFormatHTML
.HTMLBody = "<html<pre" & strBody & "</pre</html"
'.Attachments.Add ("myfilenamewithfullpath.xls")
' .Send 'Let´s go!
.Save ' stick it in drafts
End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub