CODEWITHSUNDEEP
vbaoutlook
Dennis Aikens
Dennis Aikens

Reputation: 5

How to assign a recipient to .To?

I convert a worksheet into a PDF and am trying to have that PDF emailed to me and copied to another person. All of this will be assigned to an action button/trigger.

Option Explicit

Sub SendExcelFileAsPDF()

Dim OutlookApp As Outlook.Application
Dim emItem As Object
Dim Receipt As String, Subject As String
Dim Message As String, Fname As String

Dim Recipient As Outlook.Recipient
Recipient = "[email protected]"
Subject = "Weekly Critical Items" & " " & Range("L1")
Message = Range("D2") & Range("J2") & "Weekly Critical Items submitted" & 
Range("L1") & " " & "in PDF Format"
Message = Message & vbNewLine & vbNewLine & "Offload Ops"
Fname = Application.DefaultFilePath & "/" & ActiveWorkbook.Name & ".pdf"

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Fname

Set OutlookApp = New Outlook.Application

Set emItem = OutlookApp.CreateItem(olMailItem)
With emItem
    .To = Recipient = "[email protected]"
    .Subject = Subject
    .Body = Message
    .Attachements.Add Fname
    .Send
End With
Set OutlookApp = Nothing

End Sub

The recipient line is where I am having issues. When I run the debugger, it's giving

Run-Time error '91: Object variable or with block variable not set

Upvotes: 0

Views: 148

Answers (2)

Imran Malek
Imran Malek

Reputation: 1719

This line

.To = Recipient = "[email protected]"

Should be just

.To = Recipient

Upvotes: 0

Valon Miller
Valon Miller

Reputation: 1156

I would dim recipient as string and update the .to assignment:

Change

Dim Recipient As Outlook.Recipient

.To = Recipient = "[email protected]"

to

Dim Recipient As string

.To = Recipient

Upvotes: 1

Related Questions