Skip to main content

Excel macro to send mail automatically


Excel  macro to send mail automatically

Summary :
This is example of macro which helps to send mail automatically with attachment of entire Workbook and pasting one particular sheet in the mail body.


 Function RangetoHTML(rng As Range)  
 ' Changed by Ron de Bruin 28-Oct-2006  
 ' Working in Office 2000-2010  
   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&quot ;)  
   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  
 Sub Mail_Selection_Outlook_Body()  
 ' Don't forget to copy the function RangetoHTML in the module.  
 ' Working in Office 2000-2010  
   Dim rng As Range  
   Dim OutApp As Object  
   Dim OutMail As Object  
   With Application  
     .EnableEvents = False  
     .ScreenUpdating = False  
   End With  
   Set rng = Nothing  
   On Error Resume Next  
   Set rng = ActiveSheet.Range("A2:AM55") 'Selection.SpecialCells(xlCellTypeVisible)  
   On Error GoTo 0  
   If rng Is Nothing Then  
     MsgBox "The selection is not a range or the sheet is protected" & _  
         vbNewLine & "please correct and try again.", vbOKOnly  
     Exit Sub  
   End If  
   Set OutApp = CreateObject("Outlook.Application")  
   Set OutMail = OutApp.CreateItem(0)  
   On Error Resume Next  
   With OutMail  
     .To = " email@removed "  
     .CC = " email@removed "  
     .BCC = " email@removed "  
     .Introduction = "TEST - " & Format(Date, "04-03-2010")  
     .Subject = "Zonal Review"  
     .HTMLBody = RangetoHTML(rng)  
     .Attachments.Add ActiveWorkbook.FullName  
     .Send  
   End With  
   On Error GoTo 0  
   With Application  
     .EnableEvents = True  
     .ScreenUpdating = True  
   End With  
   Set OutMail = Nothing  
   Set OutApp = Nothing  
 End Sub  

Popular posts from this blog

Resolved : Power BI Report connection error during execution

Getting Below Power BI Report connection error during execution . Error: Something went wrong Unable to connect to the data source undefined. Please try again later or contact support. If you contact support, please provide these details. Underlying error code: -2147467259 Table: Business Sector. Underlying error message: AnalysisServices: A connection cannot be made. Ensure that the server is running. DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259 Microsoft.Data.Mashup.ValueError.DataSourceKind: AnalysisServices Microsoft.Data.Mashup.ValueError.DataSourcePath: 10.10.10.60;T_CustomerMaster_ST Microsoft.Data.Mashup.ValueError.Reason: DataSource.Error Cluster URI: WABI-WEST-EUROPE-redirect.analysis.windows.net Activity ID: c72c4f12-8c27-475f-b576-a539dd81826a Request ID: dfb54166-c78f-4b40-779f-e8922a6687ad Time: 2019-09-26 10:03:29Z Solution: We found report connection not able to connect to SQL Analysis service so tried below option. Re

Song- Khamoshiyan Piano keyboard Chord,Notation and songs Lyrics

All songs notation and chords at one place

Song : O Saathi Re Film : Mukhathar Ka Sikkandhar Uses : C D D# E G A Note : The numbers at the end of the lines indicate line numbers. Pallavi: O saathi re, tere binaa bhi kya jina, tere binaa bhi kya jina A- C D D#....,D D C DD E...C..CA-...,D D C DD E...CC.......1 Play line 1 again phulon men khaliyon men sapnom ki galiyon men GGG...GAGE.. GGG G A G E.................................................2 tere bina kuchh kahin naa E A G E D C D D#.......................................................................3 tere binaa bhi kya jina, tere binaa bhi kya jina D D C DD E....C..CA-..., D D C DDE....CC.............................4 Charanam: har dhadkan men, pyaas hai teri, sanson men teri khushboo hai CCC C D C A-, CCC C D C A-, DDD DED CD EE.. CCCC......................5 is dharthi se, us ambar tak, meri nazar men tu hi tu hai CCC C D C A-, CCC C D C A-, DDD DED CD EE.. CCCC......................6 pyaar yeh tute naa GGG... GAG D#......E.........................