☰

更改此代码以 XLSX 格式发送附件

tag-icon • tag-icon microsoft-excel macros code
更改此代码以 XLSX 格式发送附件

我需要更改此代码以将某个区域作为 Outlook 中的 xlsx 附件发送，而不是作为 pdf 发送。

Sub EnviarEmailPeloExcelAnexoPDF()
    Dim sPara      As String
    Dim sMsg       As String
    Dim sAssunt    As String
    Dim PdfCaminho As String
    Dim PdfNome    As String
    
    '            ALTERE O CAMINHO Q SERA SALVO O PDF SE NECCESS.
    PdfCaminho = VBA.Environ("X_BOASJ") & "C:\Users\boasj\OneDrive - Tate & Lyle\Desktop\"
    '            ALTERE O NOME DO PDF DE ACORDO COM A NECESS.
    PdfNome = "FolhaFrequência" & VBA.Format(VBA.Now, "yyyy-mm-dd") & ".pdf"
    
    ThisWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypeXLSM, Filename:= _
    PdfCaminho & PdfNome, Quality:= _
    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

    sPara = Range("R3").Value
    
    If Not sPara = "" Then
        sAssunt = Range("B9").Value
        sMsg = "Olá," & vbNewLine & vbNewLine & "Segue em anexo sua folha frequência deste mês, favor preencher e ao final do mês nos retornar com a aprovação de seu gestor."
        Envia_Emails sPara, sMsg, sAssunt, PdfCaminho & PdfNome
    End If
    
End Sub

Sub Envia_Emails(sPara As String, sMsg As String, sAssunt As String, PdfAnexo As String)
    Dim OutlookApp   As Object
    Dim OutlookMail  As Object
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItem(0)
    
    With OutlookMail
        .To = sPara
        .CC = ""
        .BCC = ""
        .Subject = sAssunt
        .Body = sMsg
        .Attachments.Add PdfAnexo
        .Display ' para envia o email diretamente defina o código  .Send
        
    End With
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
End Sub

答案1

首先更改文件名中的文件扩展名：

PdfNome = "FolhaFrequência" & VBA.Format(VBA.Now, "yyyy-mm-dd") & ".xlsx"

然后另存为而不是导出。因此：

ThisWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypeXLSM, Filename:= _
    PdfCaminho & PdfNome, Quality:= _
    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

变成：

ThisWorkbook.ActiveSheet.SaveAs FileName:=PdfCaminho & PdfNome, FileFormat:=xlWorkbookDefault

我希望这有帮助。

相关内容