I found a template for a code online to auto-generate an Outlook e-mail from a spreadsheet. The code is working perfectly (displays exactly what I want in the e-mail), but if I hit "send" or change .display to .send, it gives me an error (and I can't find out what error it is, as my msgbox returns "0:"). Any ideas on what is causing this?
Sub EMail_Outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Dim Lstr(2 To 11)
Dim Rstr(2 To 11)
Dim Tstr(2 To 11)
Dim i As Integer
Workbooks.Open ("C:\Users\test\Desktop\Training\% Training Completion.xlsx")
For i = 2 To 11
Cells(i, 4).Value = 100 * Cells(i, 3).Value
Next i
Range("D2:D11").Select
Selection.NumberFormat = "0.00"
For i = 2 To 11
Lstr(i) = Workbooks("% Training Completion.xlsx").Worksheets("Sheet1").Cells(i, 1).Value
Rstr(i) = Workbooks("% Training Completion.xlsx").Worksheets("Sheet1").Cells(i, 4).Value
Tstr(i) = Lstr(i) & " - " & Rstr(i) & "%"
Next i
Range("D2:D11").Delete
ActiveWorkbook.Save
ActiveWorkbook.Close
strbody = "Hi test," & vbNewLine & vbNewLine & _
"The % Training Completion for test is:" & vbNewLine & vbNewLine & _
Tstr(2) & vbNewLine & _
Tstr(3) & vbNewLine & _
Tstr(4) & vbNewLine & _
Tstr(5) & vbNewLine & _
Tstr(6) & vbNewLine & _
Tstr(7) & vbNewLine & _
Tstr(8) & vbNewLine & _
Tstr(9) & vbNewLine & _
Tstr(10) & vbNewLine & _
Tstr(11)
On Error Resume Next
With OutMail
.To = "testl@gmail.com"
.CC = ""
.BCC = ""
.Subject = "test"
.Body = strbody
With .attachments
.Add ("C:\Users\test\Desktop\Training\% Training Completion.xlsx")
.Add Application.ActiveWorkbook.FullName
End With
.display
End With
On Error GoTo here
here: MsgBox Err.Number & ": " & Err.Description
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Bookmarks