在Windows和Mac上从VBA宏发送带有工作簿的电子邮件 [英] Send email with workbook from VBA macro on both Windows and Mac

问题描述

我的以下代码在PC上正常工作，但在Mac上不工作.我希望脚本能够识别当前操作系统并为该操作系统运行适当的命令集，而不是使用两个单独的按钮来为Windows和Mac用户制作宏的两个版本.

My following code works correctly on PC but does not work on a Mac. Instead of making two versions of the macro with separate buttons for Windows and Mac users, I would like the script to recognize the current OS and run the appropriate set of commands for that OS.

宏将创建带有工作簿附件的电子邮件.附件是ActiveWorkbook的临时版本，在发送电子邮件后会被删除.

The macro creates an email with a workbook attachment. The attachment is a temporary version of the ActiveWorkbook which is deleted after the email is sent.

我当前使用的发送电子邮件的方法是Windows CDO.在Mac OSX上与Office 2016一起执行时，我还有其他注意事项吗?

The method I'm currently using to send an email is Windows CDO. Are there any other considerations I should be aware of when its executing on MAC OSX with Office 2016?

Private Message As CDO.Message
Private Attachment, Expression, Matches, FilenameMatch, i

Sub enviar_mail()

    Dim wb1 As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String


    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set wb1 = ActiveWorkbook

    'Make a copy of the file/Open it/Mail it/Delete it
    'If you want to change the file name then change only TempFileName
    TempFilePath = Environ$("temp") & "\"
    TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
    FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))

    wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr

    On Error Resume Next

    Set Message = New CDO.Message
    Message.Subject = ActiveSheet.Range("G9").Value
    Message.From = ""
    Message.To = ""
    Message.CC = ""
    Message.HTMLBody = ActiveSheet.Range("A12").Value
    Message.AddAttachment TempFilePath & TempFileName & FileExtStr

    Dim Configuration
    Set Configuration = CreateObject("CDO.Configuration")
    Configuration.Load -1                        ' CDO Source Defaults
    Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.office365.com"
    Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
    Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[e-mail]"
    Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "*****"
    Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

    Configuration.fields.Update

    Set Message.Configuration = Configuration
    Message.Send

    On Error GoTo 0

    'Delete the file
    Kill TempFilePath & TempFileName & FileExtStr

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub

推荐答案

因此，要确定操作系统，可以使用

So for determining the OS, you can use conditional compilation directives like so:

#If Mac Then
    Debug.Print "I'm a Mac"
#Else
    Debug.Print "I'm not"
#End If

在现代MacOS上，由于OS内置的安全性，发送邮件非常棘手. CDO严格来说是Windows技术，在这里不适用.大多数人会编写一个单独的AppleScript文件，然后由Excel执行.请参阅此页面，以了解有关如何针对Outlook和Mail.app进行此操作的详细信息.

Sending mail is tricky on modern MacOS because of the security built in to the OS. CDO is strictly a Windows technology and does not apply here. Most people go with writing a separate AppleScript file that is then executed by Excel. See this page for details on how to do it for both Outlook and Mail.app.

当然，它确实需要额外的步骤才能首先将脚本导入用户的计算机，但是AppleScript非常容易理解.例如:

It does of course involve extra steps to get the script into the user's computer in the first place, but AppleScript is pretty straightforward to understand. For example:

tell application "Mail"
    set NewMail to (make new outgoing message with properties {subject:"My Subject"})
    tell NewMail
        set sender to "[e-mail]"
        set content to "My email message"
        make new to recipient with properties {address:"[e-mail]"}
        send
    end tell
end tell

这篇关于在Windows和Mac上从VBA宏发送带有工作簿的电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！