Hi,
I have been trying to wrist a code to send emails via excel.
All the information I need is contained within the a worksheet, including email address and email body. Email addresses are contained in Column E.
Each row contains slightly different information.
Here is the code:
Sub EmailBody()
Dim SendTo As String
Dim MailBody As String
For Each i In Range("A2:A100")
MailBody = "Dear " & i.Offset(0, 5) & "," & vbCr & vbCr & i.Offset(0, 6) & vbCr & vbCr & i.Offset(0, 7) & vbCr & vbCr & i.Offset(0, 8) & vbCr & vbCr & "Hospital Number: " & i.Value & vbCr & "Exam Date: " & i.Offset(0, 1) & vbCr & "Exam: " & i.Offset(0, 2) & vbCr & vbCr & "Kind regards," & vbCr & vbCr & i.Offset(0, 11) & vbCr & i.Offset(0, 9) & vbCr & i.Offset(0, 10)
SendTo = i.Offset(0, 5).Value
MsgBox MailBody & vbCr & vbCr & SendTo
With outMail
.To = SendTo
.Subject = "Images for QA"
.Body = MailBody
.Display
End With
Next i
End Sub
My problem here is two-fold.
1. I am getting runtime error at the .To = SendTo line (highlighted in yellow)
2. The code was originally more complicated so I have tried to simplify it. My previous problem was that the code was creating a new email for every address in column E, but was only using data from the top row of the range.
Any help would be greatly appreciated as I cannot figure it out.
I have been trying to wrist a code to send emails via excel.
All the information I need is contained within the a worksheet, including email address and email body. Email addresses are contained in Column E.
Each row contains slightly different information.
Here is the code:
Sub EmailBody()
Dim SendTo As String
Dim MailBody As String
For Each i In Range("A2:A100")
MailBody = "Dear " & i.Offset(0, 5) & "," & vbCr & vbCr & i.Offset(0, 6) & vbCr & vbCr & i.Offset(0, 7) & vbCr & vbCr & i.Offset(0, 8) & vbCr & vbCr & "Hospital Number: " & i.Value & vbCr & "Exam Date: " & i.Offset(0, 1) & vbCr & "Exam: " & i.Offset(0, 2) & vbCr & vbCr & "Kind regards," & vbCr & vbCr & i.Offset(0, 11) & vbCr & i.Offset(0, 9) & vbCr & i.Offset(0, 10)
SendTo = i.Offset(0, 5).Value
MsgBox MailBody & vbCr & vbCr & SendTo
With outMail
.To = SendTo
.Subject = "Images for QA"
.Body = MailBody
.Display
End With
Next i
End Sub
My problem here is two-fold.
1. I am getting runtime error at the .To = SendTo line (highlighted in yellow)
2. The code was originally more complicated so I have tried to simplify it. My previous problem was that the code was creating a new email for every address in column E, but was only using data from the top row of the range.
Any help would be greatly appreciated as I cannot figure it out.