Modify code to attach specific sheet as .xlsx

SaraO · Mar 9th 2016

Hi,

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?

Code

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

Display More

**pike** · Mar 10th 2016

Re: Modify code to attach specific sheet as .xlsx

hello,
you can add something like

Code

Dim wb As Workbook
    ActiveSheet.Copy
    Set wb = ActiveWorkbook
    PdfFile = ThisWorkbook.Path & "\" & ActiveSheet.Name & " " & Range("I10").Value & " " & Range("I11").Value
    With wb
        .SaveAs PdfFile
        .Close False
    End With

Modify code to attach specific sheet as .xlsx

Participate now!

Share