+ Reply to Thread
Results 1 to 13 of 13

Macro to send mail automatically

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    01-24-2013
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    100

    Macro to send mail automatically

    Hi Friends

    I want to a code which automatically sends a mail whenever the module is called. In the body of the mail i want to inculde a table of details from a excel spreadsheet.

    For eg Datas from A1:H1 needs to be included in the mail

    Pls help me with a code

  2. #2
    Forum Contributor ragavan.sridar1's Avatar
    Join Date
    11-19-2012
    Location
    India
    MS-Off Ver
    Excel 2010, Excel 2003
    Posts
    208

    Re: Macro to send mail automatically

    hi shyam,

    try this!
    sub email_12()
    
    ActiveSheet.Range("A1:H1").Select
        
       
       ' Show the envelope on the ActiveWorkbook.
       ActiveWorkbook.EnvelopeVisible = True
    
       ' Set the optional introduction field thats adds
       ' some header text to the email body. It also sets
       ' the To and Subject lines. Finally the message
       ' is sent.
       With ActiveSheet.MailEnvelope
          .Introduction = "Hi there, for you....."
          .Item.To = Range("J1") 'your address here
          .Item.Subject = "your subject"
          .Item.display
        End With
    End Sub
    Thanks!
    Raga.

    Please,mark your thread [SOLVED] if you received your answer.

    Click the little star * below, to give some Rep if you think an answer deserves it.

    I learnt so many things from these links.

  3. #3
    Forum Contributor
    Join Date
    01-24-2013
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    100

    Re: Macro to send mail automatically

    ragavan

    Thanks a lot for the code. I have tried but the entire excel sheet is reflecting in the mail body

    i want the details from A1 - H1 and the end A8 - H8

  4. #4
    Forum Contributor ragavan.sridar1's Avatar
    Join Date
    11-19-2012
    Location
    India
    MS-Off Ver
    Excel 2010, Excel 2003
    Posts
    208

    Re: Macro to send mail automatically

    Hi Shyam,

    It will reflect only the entire sheet but it will only send the selection, in this case it will only send the Range("A1:H1")

    just try to send it to your mail id and see the outcome...

  5. #5
    Forum Contributor
    Join Date
    01-24-2013
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    100

    Re: Macro to send mail automatically

    Thanks a lot i will try it can you pls advice how to include the data till A8 - H8

    sorry to ask at last

  6. #6
    Forum Contributor ragavan.sridar1's Avatar
    Join Date
    11-19-2012
    Location
    India
    MS-Off Ver
    Excel 2010, Excel 2003
    Posts
    208

    Re: Macro to send mail automatically

    try to change the data range as

    Range("A1:H8").select

  7. #7
    Forum Contributor
    Join Date
    01-24-2013
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    100

    Re: Macro to send mail automatically

    Hi Ragavan

    currently am using the below code in this am included the atachments and all . Can u pls advice how to include the code u shared in this

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
    If IsNumeric(Target.Value) And Target.Value > 200 Then
    Call YourMacroName
    End If
    End If
    End Sub


    Sub Mail_small_Text_Outlook()
    'Working in Office 2000-2010
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

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

    strbody = "Hi there" & vbNewLine & vbNewLine & _
    "Cell A1 is changed" & vbNewLine & _
    "This is line 2" & vbNewLine & _
    "This is line 3" & vbNewLine & _
    "This is line 4"

    On Error Resume Next
    With OutMail
    .To = "ron@debruin.nl"
    .CC = ""
    .BCC = ""
    .Subject = "This is the Subject line"
    .Body = strbody
    'You can add a file like this
    '.Attachments.Add ("C:\test.txt")
    .Display 'or use .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
    End Sub

  8. #8
    Forum Contributor
    Join Date
    01-24-2013
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    100

    Re: Macro to send mail automatically

    Thanks a lot for ur valuable time i will check and let u know if am having any doubts

  9. #9
    Forum Contributor
    Join Date
    01-24-2013
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    100

    Re: Macro to send mail automatically

    Ragavan

    The above code also shared from ur end only in my last post

  10. #10
    Forum Contributor ragavan.sridar1's Avatar
    Join Date
    11-19-2012
    Location
    India
    MS-Off Ver
    Excel 2010, Excel 2003
    Posts
    208

    Re: Macro to send mail automatically

    Hi shyam,

    the code which i gave is used to send only the range which is selected in our case it will only send the range("a1:h8") you cannot add any file/Signature in it.

    if you want to attach the file as well as send the range, then you can use this code..(which i browsed and got)

    Sub Mail_Selection_Range_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
    
        Set rng = Nothing
        On Error Resume Next
        'Only the visible cells in the selection
        Set rng = Selection.SpecialCells(xlCellTypeVisible)
        'You can also use a range if you want
        'Set rng = Sheets("YourSheet").Range("D4:D12").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
    
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
    
        On Error Resume Next
        With OutMail
            .To = "ron@debruin.nl"
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line"
            .HTMLBody = RangetoHTML(rng)
    'You can add a file like this
    '.Attachments.Add ("C:\test.txt")
           .Display 'or use .Send
        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)
    ' 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")
        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
    Let me know if this helps...

  11. #11
    Forum Contributor ragavan.sridar1's Avatar
    Join Date
    11-19-2012
    Location
    India
    MS-Off Ver
    Excel 2010, Excel 2003
    Posts
    208

    Re: Macro to send mail automatically

    Hi shyam,

    the code which i gave is used to send only the range which is selected in our case it will only send the range("a1:h8") you cannot add any file/Signature in it.

    if you want to attach the file as well as send the range, then you can use this code..(which i browsed and got)

    Sub Mail_Selection_Range_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
    
        Set rng = Nothing
        On Error Resume Next
        'Only the visible cells in the selection
        Set rng = Selection.SpecialCells(xlCellTypeVisible)
        'You can also use a range if you want
        'Set rng = Sheets("YourSheet").Range("D4:D12").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
    
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
    
        On Error Resume Next
        With OutMail
            .To = "ron@debruin.nl"
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line"
            .HTMLBody = RangetoHTML(rng)
    'You can add a file like this
    '.Attachments.Add ("C:\test.txt")
           .Display 'or use .Send
        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)
    ' 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")
        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
    Let me know if this helps...

  12. #12
    Forum Contributor
    Join Date
    01-24-2013
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    100

    Re: Macro to send mail automatically

    Hi Ragavan

    It working but the body of the mail is reflecting as blank i have from ur previous code to include the same format but its not working

    strbody = "Hi there" & vbNewLine & vbNewLine & _
    "Cell A1 is changed" & vbNewLine & _
    "This is line 2" & vbNewLine & _
    "This is line 3" & vbNewLine & _
    "This is line 4"

  13. #13
    Forum Contributor ragavan.sridar1's Avatar
    Join Date
    11-19-2012
    Location
    India
    MS-Off Ver
    Excel 2010, Excel 2003
    Posts
    208

    Re: Macro to send mail automatically

    Add this..

     Dim StrBody As String
    
    StrBody = "This is line 1" & "<br>" & _
                  "This is line 2" & "<br>" & _
                  "This is line 3" & "<br><br><br>"
    and change the code as

    .HTMLBody = StrBody & RangetoHTML(rng)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1