Results 1 to 4 of 4

add "SaveAs" to pdf export and email code

Threaded View

  1. #1
    Registered User
    Join Date
    11-05-2009
    Location
    Tennessee
    MS-Off Ver
    Excel 2007
    Posts
    12

    add "SaveAs" to pdf export and email code

    I have the following code working well. I'd like the pdf to be saved to the users local machine before it emails it. The user must be able to choose the path. How can I do this?

    'save and email as pdf
    Sub Approved()
      Dim IsCreated As Boolean
      Dim i As Long
      Dim PdfFile As String, Title As String
      Dim OutlApp As Object
      Dim PointsAwarded As String
      Dim TechName As String
     
      ' Not sure for what the Title is
      Title = Range("A1")
     
      ' Define PDF filename
      PdfFile = ActiveWorkbook.FullName
      i = InStrRev(PdfFile, ".")
      If i > 1 Then PdfFile = Left(PdfFile, i - 1)
      PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf"
      
      'Define PointsAwarded
      PointsAwarded = Range("W7").Value
      
      'Define TechName
      TechName = Range("J11").Value
     
      ' 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 = "" ' <-- Put email of the recipient here
        .CC = "" ' <-- Put email of 'copy to' recipient here
        .Body = "The attached Award Points Request has received Department Head Approval" & vbLf & vbLf _
              & PointsAwarded & " points will be awarded to " & TechName & "." & vbLf & vbLf _
              & "Regards," & 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
     
    End Sub
    Last edited by matt5596; 02-04-2014 at 12:37 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need VBA Code to "redirect" not "forward" an email
    By derrikhu in forum Outlook Programming / VBA / Macros
    Replies: 0
    Last Post: 09-08-2013, 10:59 PM
  2. Replies: 0
    Last Post: 11-20-2012, 10:22 AM
  3. Export "Unformatted" Data from Email to Excel
    By timdecker in forum Outlook Programming / VBA / Macros
    Replies: 2
    Last Post: 09-29-2012, 06:06 PM
  4. Macro Error with code "Range("A65536").End(xlUp).EntireRow.Insert"
    By lukasj13 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-30-2010, 08:48 PM
  5. Modify code to email a selected range or ("print area") in lieu of ActiveSheet
    By robertse in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-15-2010, 05:15 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1