I have created the following Macro based off Ron de Bruin's templates here http://www.rondebruin.nl/win/s1/outlook/bmail5.htm
It was working fine until I added more code to the strbody, but now, the macro runs through, no errors come up, and nothing gets sent. Any help/ideas?
Sub SendingEmail()
'
' SendingEmail Macro
'
'Working in Excel 2002-2013
'Working in Office 2000-2013
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error GoTo cleanup
For Each cell In Columns("K").Cells.SpecialCells(xlCellTypeConstants)
If Cells(cell.Row, "K").Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "O").Value) <> "send" Then
strbody = "Dear " & Cells(cell.Row, "A").Value & vbNewLine & vbNewLine & _
"Thank you for your interest in the new b. Per your request, we will shortly be sending out a sample pack with four " & _
"sensors and a connection cable. To ensure that your receive the sample pack, please review and confirm the shipping address below." & vbNewLine & _
"Please reply with 'OK' if the address is correct as listed below, or reply with your corrected address. You will receive an additional email from us to confirm " & _
"shipment of the sample pack to you." & vbNewLine & _
"We would also like to be able to contact you by phone or email several weeks after you receive your sample pack to get your feedback on the product. Please " & _
"note that we respect your time and privacy. Your contact information will be used solely for the purpose of delivering the sample kit, confirming your receipt and " & _
"follow-up to get your feedback." & vbNewLine & _
"This is the contact information that we have: " & vbNewLine & _
Cells(cell.Row, "B ").Value & _
Cells(cell.Row, "A ").Value & _
Cells(cell.Row, "C").Value & _
Cells(cell.Row, "D").Value & _
Cells(cell.Row, "E").Value & _
Cells(cell.Row, "F ").Value & _
Cells(cell.Row, "G:I").Value & _
Cells(cell.Row, "J").Value & vbNewLine & _
"To view our demonstration video on how to use the b, please visit: www.b.com/demonstration" & vbNewLine & _
"Again, thank you for your interest. We look forward to your feedback." & vbNewLine & _
"Best regards," & vbNewLine & _
"The b Customer Service Team"
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = Cells(cell.Row, "K").Value
.CC = ""
.BCC = ""
.Subject = "bc"
.Body = strbody
'You can add a file like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0
Cells(cell.Row, "O").Value = "send"
End If
Next cell
cleanup:
Set OutMail = Nothing
Set OutApp = Nothing
'
End Sub
Bookmarks