I am using this code to send and attachment through outlook as a PDF. Now I need to send that same attachment as a .xlsx. Also I would like it to be a specific sheet that is attached, not active sheet.
How can this code be modified?
Sub AttachActiveSheetPDF() Dim IsCreated As Boolean Dim i As Long Dim PdfFile As String, Title As String Dim OutlApp As Object Application.ScreenUpdating = False Title = Range("I10") ' Define PDF filename PdfFile = ActiveWorkbook.FullName i = InStrRev(PdfFile, ".") If i > 1 Then PdfFile = Left(PdfFile, i - 1) PdfFile = PdfFile & " " & ActiveSheet.Name & " " & Range("I10").Value & " " & Range("I11").Value & ".pdf" ' Export activesheet as PDF With ActiveSheet .ExportAsFixedFormat Type:=xlTypePDF, FileName:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False End With ' Use already open Outlook if possible On Error Resume Next Set OutlApp = GetObject(, "Outlook.Application") If Err Then Set OutlApp = CreateObject("Outlook.Application") IsCreated = True End If OutlApp.Visible = True On Error GoTo 0 ' Prepare e-mail with PDF attachment With OutlApp.CreateItem(0) ' Prepare e-mail .Subject = Title .To = Range("B13").Value '.CC = "" .Body = "Hi," & vbLf & vbLf _ & "New Work Order is attached." & vbLf & vbLf _ & "Thank you," & vbLf _ & Application.UserName & vbLf & vbLf .Attachments.Add PdfFile ' Try to send On Error Resume Next .Send Application.Visible = True If Err Then MsgBox "E-mail was not sent", vbExclamation Else MsgBox "E-mail successfully sent", vbInformation End If On Error GoTo 0 End With ' Delete PDF file Kill PdfFile ' Quit Outlook if it was created by this code If IsCreated Then OutlApp.Quit ' Release the memory of object variable Set OutlApp = Nothing Application.ScreenUpdating = True End Sub