Richard has attended:
Excel VBA Intro Intermediate course
Auto email problems- runtime error '13'
i'm Trying to auto email using a userform in VBA, adapting a code i got off a colleague.
however when i try and run i get a run-time error '13' and debug highlights
Set ObjMail = ObjOutlook.CreateItem(olMailItem)
the full code is quite long and complicated but looks like this...
Dim ObjOutlook As Object
Dim ObjMail As Object
Dim TxtSubj As String
Dim TxtBody As String
Dim Duration As String
Dim A As String
Dim B As String
Dim C As String
Dim D As String
Dim E As String
Dim F As String
Dim RowCount As Integer
Dim Email As String
Dim Req As String
Set ObjOutlook = CreateObject("Outlook.Application")
Set ObjMail = ObjOutlook.CreateItem(olMailItem)
For RowCount = 2 To Sheets("Requestor").Cells(1, 1).CurrentRegion.Rows.Count
If Req = Sheets("Requestor").Cells(RowCount, 1) Then Email = Sheets("Requestor").Cells(RowCount, 2)
Next RowCount
If Week12 = True Then Duration = "12 weeks" Else Duration = "24 weeks"
If Freezer = True Then A = "Freezer "
If Oven = True Then B = "Oven "
If Fridge = True Then C = "Fridge "
If Light = True Then D = "Light "
If Dark = True Then E = "Dark." Else E = "."
If Water = True Then F = "Water Tolerence Test Was Selected" Else F = "Water Tolerence Test Was Not Selected"
TxtSubj = "Test for Sample: " & Bcode
TxtBody = "Your test for sample" & Bcode _
& ", was started on " & Started & " and will run for " & Duration & "in the following conditions:" _
& vbCrLf & vbCrLf _
& A & B & C & D & E _
& vbCrLf & vbCrLf _
& F _
& vbCrLf & vbCrLf _
& "It's progress can be tracked at <website redacted for security>"
With ObjMail
.Subject = TxtSubj
.Body = TxtBody
.Recipients.Add Email
.Send
End With
RE: Auto email problems- runtime error '13'
Hi Richard
Thanks for getting in touch. Whenever you are interacting with another Office application from within VBA, you have to load in that application's code objects as a reference.
Within the Visual Basic Editor, go to Tools > References. Scroll through the list to find "Microsoft Outlook 15.0 Object Library" (if the number is different that's okay, just use the highest version you can). Check that box, restart Excel and try to run your code again.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector