时间戳

首页 > 解决方案 > How to assign a recipient to .To?

vba - 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 = "xxxxx.xxxxx@fedex.com"
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 = "xxxxx.xxxxx@fedex.com"
    .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

标签： vbaoutlook

解决方案

我会将收件人变暗为字符串并更新 .to 分配：

改变

Dim Recipient As Outlook.Recipient

.To = Recipient = "dennis.aikens@fedex.com"

至

Dim Recipient As string

.To = Recipient

推荐阅读