我有一个大excel文件,该文件带有命令按钮,将电子邮件发送给工作中的管理人员,然后他们可以按下按钮,然后将文件发送给下面的管理人员。
由于每个经理都有她/他自己的MS Office
版本,因此我有一个子检查他在她/他的计算机上的版本,并在References
中标记V
。
保存文件时,我将其保存在Outlook Object Library
没有用V
标记的状态中,并且我有其他人构建的代码。该代码通过3个潜艇运行。第一个子具有msgbox
,当您在其上回答时,是,它会将您发送到下一个子。
Public Sub before_send_mail()
answer = MsgBox("Send Email?", vbYesNo + vbQuestion, "Empty Sheet")
If answer = vbYes Then
Call excel_ver
Call sendMail
Call remove_ref
Else
'do nothing
End If
End Sub
然后,我有"按办公室版本的参考选择器"检查计算机上安装了哪个版本,并在Outlook
对象中的Tools---->References
中自动标记V
。这部分似乎也很好。
Sub excel_ver()
On Error Resume Next
ver = Application.Version
If ver = 16 Then
tmp_name = "C:Program FilesMicrosoft OfficeOffice16MSOUTL.OLB"
Application.VBE.ActiveVBProject.References.AddFromFile tmp_name
Exit Sub
End If
If ver = 15 Then
tmp_name = "C:Program FilesMicrosoft OfficeOffice15MSOUTL.OLB"
Application.VBE.ActiveVBProject.References.AddFromFile tmp_name
Exit Sub
End If
If ver = 14 Then
tmp_name = "C:Program FilesMicrosoft OfficeOffice14MSOUTL.OLB"
Application.VBE.ActiveVBProject.References.AddFromFile tmp_name
Exit Sub
End If
End Sub
,然后我们解决了问题。当我到达sub sendMail
时,它给了我在行Dim applOL As Outlook.Application
Public Sub sendMail()
Call ini_set
If mail_msg.Cells(200, 200) = 1 Then
lr = main_dist.Cells(main_dist.Rows.Count, "A").End(xlUp).Row
On Error Resume Next
For i = 2 To lr
Application.DisplayAlerts = False
Dim applOL As Outlook.Application 'Here is the error ---- that line
Dim miOL As Outlook.MailItem
Dim recptOL As Outlook.Recipient
mail_msg.Visible = True
mailSub = mail_msg.Range("B1")
mailBody = mail_msg.Range("B2")
mail_msg.Visible = False
Set applOL = New Outlook.Application
Set miOL = applOL.CreateItem(olMailItem)
Set recptOL = miOL.Recipients.Add(main_dist.Cells(i, 5))
recptOL.Type = olTo
tempPath = ActiveWorkbook.Path & "" & main_dist.Cells(i, 4) & ".xlsm"
With miOL
.Subject = mailSub
.Body = mailBody
.Attachments.Add tempPath
.send
End With
Set applOL = Nothing
Set miOL = Nothing
Set recptOL = Nothing
Application.DisplayAlerts = True
Next i
End If
End Sub
应该不需要参考:
Public Sub sendMail()
Dim applOL As Object, miOL As Object, recptOL As Object
Dim i As Long
ini_set
If mail_msg.Cells(200, 200) = 1 Then
Set applOL = CreateObject("Outlook.Application")
For i = 2 To main_dist.Cells(main_dist.Rows.Count, "A").End(xlUp).Row
Set miOL = applOL.CreateItem(0) 'olMailItem=0
Set recptOL = miOL.Recipients.Add(main_dist.Cells(i, 5))
recptOL.Type = 1 ' olTo=1
With miOL
.Subject = mail_msg.Range("B1")
.Body = mail_msg.Range("B2")
.Attachments.Add ActiveWorkbook.Path & "" & _
main_dist.Cells(i, 4) & ".xlsm"
.send
End With
Next i
Set applOL = Nothing
End If
End Sub
编辑:在上面的代码中,我删除了您的一些"一次性"变量,但这只是我的喜好...
在预编译过程中Outlook.Application
无效,因为它在ToolsReferences...
中未设置。如果要保持代码工作,则需要先运行ini_set
,甚至还需要编译sendMail
之前。尝试添加新的子例程以按顺序调用这两个:
Sub MainSub()
call ini_set
call sendMail
End Sub
使其清晰 - 从您的sendMail
中删除Call ini_set
,每次您都必须从单独的子例程中拨打这两者。
wittes!使用此解决方案,您可以保留outlook appilcation constants
(如olMailItem
),当您切换到Late binding solution
时,这是不可能的。