Link to home
Start Free TrialLog in
Avatar of kausalya durgale
kausalya durgale

asked on

Automatic emsil to be sent to Mutliple Recipients from Excel to outlook

Hi There, I am using below code to send mail automatically from excel which has some range of data to b sent. I want to send mail to many recipients in To and CC, where should I modify, below code is working fine to send email to my own id: also this mail goes everyday and the date in the subject line should automatically consider current date of every day.

 Sub Mail_Sheet_Outlook_Body()
     Dim rng As Range
     Dim OutApp As Object
     Dim OutMail As Object
     
     With Application
         .EnableEvents = False
         .ScreenUpdating = False
     End With

     Set rng = Nothing
     Set rng = ActiveSheet.UsedRange
     'You can also use a sheet name
     'Set rng = Sheets("YourSheet").UsedRange

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

     On Error Resume Next
     With OutMail
         .To = "kausalya.durgale@accenture.com"
         .CC = ""
         .BCC = ""
         .Subject = "Service Check 12-01-2018,NEED YOUR CONFIRMATION by 01:30 PM IST "
         .HTMLBody = RangetoHTML(rng)
         .Send   'or use .Display
     End With
     On Error GoTo 0
     

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

     Set OutMail = Nothing
     Set OutApp = Nothing
 End Sub


 Function RangetoHTML(rng As Range)
     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
ASKER CERTIFIED SOLUTION
Avatar of yo_bee
yo_bee
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kausalya durgale
kausalya durgale

ASKER

Thank you for the response! I have replaced mail id's with ; which i was trying initially by separating comma's. hope this will work the same way when I add recipients in CC list as well..

Also, as this is a daily mailer, can i get current date in the subject line after "service health check" else we have to modify the code everyday to change the date before we run the code.
Email need semi-columns. Email address display names could have commas and they are not used as delimiters for this reason.