add mail body from excel range vba

Question

I have a database, just with names and e-mail addresses and I need to create individual e-mails with a specific fixed text, that is in a specific range of my excel and save it in a specific folder of my hard drive.

However, I'm having problems with the body text.

Here it is the code:

Sub test()
Dim Sendrng As Range
Dim outlookApp As Outlook.Application
Dim outlookMail As Outlook.MailItem
Dim i As Integer


For i = 2 To ActiveSheet.Cells(5, 2).Value + 1

    Set Sendrng = ActiveSheet.Range("B12:K29")
    Set outlookApp = New Outlook.Application
    Set outlookMail = outlookApp.CreateItem(olMailItem)
    With outlookMail
        .To = ActiveSheet.Cells(7, 2).Value
        .Subject = ActiveSheet.Cells(8, 2).Value
        '.HTMLBody = Sendrng
        .Save
    End With

    i = i + 1

    ActiveSheet.Cells(4, 2) = i

    Set outlookMail = Nothing
    Set outlookApp = Nothing

Next i

'I want to start every time in position 2
ActiveSheet.Cells(4, 2) = 2

End Sub

Can you explain to me where is missing? I'm getting the following error: Run-time Error 13: Type mismatch

Thanks, Henrique

score 0 · Answer 1 · answered May 10 '18 at 23:48

You need to use the Text property of the Range class instead of Value:

Sub test()
  Dim Sendrng As Range
  Dim outlookApp As Outlook.Application
  Dim outlookMail As Outlook.MailItem
  Dim i As Integer


  For i = 2 To ActiveSheet.Cells(5, 2).Value + 1
    Set Sendrng = ActiveSheet.Range("B12:K29")
    Set outlookApp = New Outlook.Application
    Set outlookMail = outlookApp.CreateItem(olMailItem)
    With outlookMail
      .To = ActiveSheet.Cells(7, 2).Text
      .Subject = ActiveSheet.Cells(8, 2).Text
      .Save
    End With
    i = i + 1
    ActiveSheet.Cells(4, 2) = i
    Set outlookMail = Nothing
    Set outlookApp = Nothing

  Next i

 'I want to start every time in position 2
 ActiveSheet.Cells(4, 2) = 2

End Sub

add mail body from excel range vba

1 Answers1