Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
E-Mail attachment to same e-mail address in Outlook | Excel Discussion (Misc queries) | |||
Macro to send a mail to outlook | Excel Discussion (Misc queries) | |||
e-mail from excel when outlook is not in use | Excel Discussion (Misc queries) | |||
is it possible to link a cell in excel to a mail in outlook? | Excel Discussion (Misc queries) | |||
Excel/Outlook Mail Merge??? | Excel Discussion (Misc queries) |