Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sending multipul e-mail from excel
,I created a work book colum A,B,C,D are full of text, colum E is the
coresponding e-mail addresses for each row there is 50 rows, is there a way to send all thoes e-mails out at once??? Thanks Microsoft Office 2003 Version Students and Teachers Edition Windows Vista Home Premium Thank-you Carl R |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sending multipul e-mail from excel
Hello.
you could use this which will loop through until it reaches the bottom of the data in column E Sub Email() Dim Lineindex As Integer Dim OutApp As Outlook.Application Dim OutMsg As MailItem Dim MyEmailAddress As String Dim rng As Range Lineindex = 1 Do Until Range("E" & Lineindex) = "" MyEmailAddress = Range("E" & Lineindex).Value Set rng = Range("A" & Lineindex & ":D" & Lineindex) Set OutApp = CreateObject("Outlook.Application") Set OutMsg = OutApp.CreateItem(olMailItem) With OutMsg .To = MyEmailAddress .Subject = "Store Delivery and Turnaround Performance Report for " '.body = "Please see the attached file." & vbCr .HTMLBody = RangetoHTML(rng) '.Attachments.Add "C:\TEMP\Delivery Performance.xls" .Send End With Lineindex = Lineindex + 1 MyEmailAddress = "" Loop Range("A1").Select End Sub And you will also need this function Function RangetoHTML(rng As Range) ' Changed by Ron de Bruin 28-Oct-2006 ' Working in Office 2000-2007 Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With 'Publish the sheet to a htm file With TempWB.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=TempWB.Sheets(1).Name, _ Source:=TempWB.Sheets(1).UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With 'Read all data from the htm file into RangetoHTML Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ "align=left x:publishsource=") 'Close TempWB TempWB.Close savechanges:=False 'Delete the htm file we used in this function Kill TempFile Set ts = Nothing Set fso = Nothing Set TempWB = Nothing End Function -- Kevin Smith :o) "Carl R" wrote: ,I created a work book colum A,B,C,D are full of text, colum E is the coresponding e-mail addresses for each row there is 50 rows, is there a way to send all thoes e-mails out at once??? Thanks Microsoft Office 2003 Version Students and Teachers Edition Windows Vista Home Premium Thank-you Carl R |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel file sending an e-mail, is possible? | Excel Discussion (Misc queries) | |||
sending an excel attachment in an e-mail | Excel Discussion (Misc queries) | |||
Sending One Cell Through Mail from Excel | Excel Worksheet Functions | |||
Sending an e-mail from the Excel or Word toolbar | Excel Discussion (Misc queries) | |||
General mail failure when sending e-mail from Excel | Excel Discussion (Misc queries) |