+ Reply to Thread
Results 1 to 4 of 4

Vba Code: Send email

Hybrid View

vidyuthrajesh Vba Code: Send email 05-25-2012, 05:38 PM
Domenic Re: Vba Code: Send email 05-25-2012, 06:39 PM
vidyuthrajesh SOLVED Re: Vba Code: Send... 05-28-2012, 12:25 PM
Domenic Re: Vba Code: Send email 05-28-2012, 12:41 PM
  1. #1
    Forum Contributor
    Join Date
    02-22-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    102

    Vba Code: Send email

    Hello,

    I did some browsing to obtain sample codes ( including rondebruin) but unfortunately i could not match available codes with my requirement and i am sure it should be there as my requirement is not very unique. Would be great if i get assistance on this. My requirement is : Pick up email address from a row and send the values in the rest of the row in Email body ( in HTML Format)


    Col A1 to E1 has column names / Headers
    Column A has Email address and B:E has other data

    the code should pick up email address starting A2 and should send the other columns ( B2:E2) in Mail Body in HTML Format ( Outlook). ( along with Headers)
    This should continue till end of used rows

    My apologies for seeking assistance for the entire code

    Thanks.

  2. #2
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

    Re: Vba Code: Send email

    Try the following code, which uses the function RangetoHTML by Ron de Bruin...

    Option Explicit
    
    Sub test()
    
        Dim OL As Object
        Dim MI As Object
        
        Dim HeaderRng As Range
        Dim DataRng As Range
        Dim LastRow As Long
        Dim i As Long
        
        Set OL = CreateObject("Outlook.Application")
        
        Set HeaderRng = Range("b1:e1")
        
        LastRow = Cells(Rows.Count, "a").End(xlUp).Row
        
        Application.ScreenUpdating = False
        
        For i = 2 To LastRow
            If Cells(i, "a").Value <> "" Then
                Set DataRng = Union(HeaderRng, Range(Cells(i, "b"), Cells(i, "e")))
                Set MI = OL.CreateItem(0)
                With MI
                    .To = Cells(i, "a").Value
                    .Subject = "Your subject here..."
                    .HTMLBody = RangetoHTML(DataRng)
                    .Display '.Send
                End With
            End If
        Next i
        
        Application.ScreenUpdating = True
        
    End Sub

  3. #3
    Forum Contributor
    Join Date
    02-22-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    102

    SOLVED Re: Vba Code: Send email

    Thanks Domenic,


    Your assistance is really helpful and it did resolve my issue. I appended this code with RangetoHTML Function which I found in http://www.rondebruin.nl/mail/folder3/mail5.htm and it is 100% resolved.

    Thanks gain.

  4. #4
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

    Re: Vba Code: Send email

    You're very welcome! Thanks for the feedback!

+ 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