How to generate an email from Excel which takes into account however many rows are selected

Question

I'm trying to generate an email from data input on to a spreadsheet, to create an offer of work. We have a list of work and assign it to someone.

At the moment with my code below, I can send one offer per email by selecting the row with the work, and pressing the command button.

However, I might be offering someone up to 4 pieces of work, and ideally I would like be able to edit this code to include all rows selected.

Does anyone have any suggestions?

Private Sub Generate_offer()

Dim strFile As String
Dim OutApp As Object
Dim objOutlookMsg As Object

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

With objOutlookMsg
.SentOnBehalfOfName = ""
.to = ""
.Subject = ""
.HTMLBody = "<p style='font-family:arial;font-size:16'> Dear <br/><br/> 

[Body of email - CUT]

& "<p style='font-family:arial;font-size:14'><b>Offer</b>: " & Cells(ActiveCell.Row, "C").Value & "<br/>" _
& "<b>Dates</b>: " & Cells(ActiveCell.Row, "L").Value & " - " & Cells(ActiveCell.Row, "M").Value & "<br/>" _
& "<b>Approx. duration</b>: " & Cells(ActiveCell.Row, "P").Value & " weeks" & "<br/>" _
& "<b>Detils</b>: xxxxx - " & Cells(ActiveCell.Row, "F").Value & "; xxxxx - " & Cells(ActiveCell.Row, "G").Value & "; xxxxx - " & Cells(ActiveCell.Row, "H").Value & "<br/><br/>" & vbNewLine _

[Body of email - CUT]

.display
  End With

  'objOutlookMsg.Send
  Set OutApp = Nothing
End Sub

Any help much appreciated.

Does the code work to send the single offer? If the code works and you want more code to extend its functionality, its probably off topic for SO. You may need to pay someone to do this for you. — Mark Fitzgerald, Apr 16 '19 at 12:24

score 1 · Accepted Answer · answered Apr 16 '19 at 13:44

Notice a couple things in the example below...

(Almost) never use Select. Your code is one case where you are requiring the user to select a set of offers in order to run the macro. The Selection only appears on one line though. The rest of the code only uses the established range variable offers.
Make sure your ranges are always fully qualified. For you, this means not using Cells all by itself but setting up a range variable (offers in my example) that is fully qualified and using that as the base for all your Cells references.

It would look something like this:

Private Sub Generate_offer()
    Dim strFile As String
    Dim OutApp As Object
    Dim objOutlookMsg As Object

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

    With objOutlookMsg
        .SentOnBehalfOfName = ""
        .To = ""
        .Subject = ""
        .HTMLbody = "<p style='font-family:arial;font-size:16'> Dear <br/><br/> "
        .HTMLbody = .HTMLbody & "[Body of email - CUT]"

        '--- assumes that the active selection is a set of rows,
        '    each row with unique offer details
        Dim offers As Range
        Set offers = ActiveSheet.Range.Selection
        Dim i As Long
        For i = 1 To offers.Rows.Count
            .HTMLbody = .HTMLbody & "<p style='font-family:arial;font-size:14'><b>Offer</b>: "
            .HTMLbody = .HTMLbody & offers.Cells(i, "C").Value & "<br/>"
            .HTMLbody = .HTMLbody & "<b>Dates</b>: " & offers.Cells(i, "L").Value
            .HTMLbody = .HTMLbody & " - " & offers.Cells(i, "M").Value & "<br/>"
            .HTMLbody = .HTMLbody & "<b>Approx. duration</b>: " & offers.Cells(i, "P").Value
            .HTMLbody = .HTMLbody & " weeks" & "<br/>"
            .HTMLbody = .HTMLbody & "<b>Details</b>: xxxxx - " & offers.Cells(i, "F").Value
            .HTMLbody = .HTMLbody & "; xxxxx - " & offers.Cells(i, "G").Value
            .HTMLbody = .HTMLbody & "; xxxxx - " & offers.Cells(i, "H").Value
            .HTMLbody = .HTMLbody & "<br/><br/>" & vbNewLine
        Next i
        .HTMLbody = .HTMLbody & [Body of email - CUT]
        .display
    End With

    'objOutlookMsg.Send
    Set OutApp = Nothing
End Sub

Hi Peter, this is really helpful, thank you, and I think it could really work. I'm getting an error when I try to run the macro though - "Run-time error '450': Wrong number of arguments or invalid property assignment", and the debugger highlights this line: "Set offers = ActiveSheet.Range.Selection". Do you know why that may be? Thanks in advance — Liz H, Apr 16 '19 at 14:11

How to generate an email from Excel which takes into account however many rows are selected

1 Answers1