+ Reply to Thread
Results 1 to 3 of 3

vba code to auto print save and email client

  1. #1
    Registered User
    Join Date
    09-04-2017
    Location
    Gold Coast, Australia
    MS-Off Ver
    Home & Student 2016
    Posts
    2

    vba code to auto print save and email client

    I need some help with my vba code i have a spreadsheet created as an auto invoice that saves as a pdf under a file path according to the invoice number in cell k9 and i am trying to automate the emailing of the invoice to clients email address based in cell h15 but it continues to get stuck on the attachments file path
    Please Help
    See Code below


    Sub create_and_email_pdf()

    Dim EmailSubject As String, EmailSignature As String
    Dim CurrentMonth As String, DestFolder As String, PDFFile As String
    Dim Email_To As String, Email_CC As String, Email_BCC As String
    Dim OpenPDFAfterCreating As Boolean, AlwaysOverwritePDF As Boolean, DisplayEmail As Boolean
    Dim OutlookApp As Object, OutlookMail As Object
    CurrentMonth = ""

    ' *****************************************************
    ' ***** You Can Change These Variables *********

    EmailSubject = "Peachy Pools Invoice " 'Change this to change the subject of the email. The current month is added to end of subj line
    OpenPDFAfterCreating = True 'Change this if you want to open the PDF after creating it : TRUE or FALSE
    AlwaysOverwritePDF = False 'Change this if you always want to overwrite a PDF that already exists :TRUE or FALSE
    DisplayEmail = True 'Change this if you don't want to display the email before sending. Note, you must have a TO email address specified for this to work
    Email_To = ActiveSheet.Range("H15") 'Change this if you want to specify To email e.g. ActiveSheet.Range("H1") to get email from cell H1
    Email_CC = ""
    Email_BCC = ""

    ' ******************************************************

    Dim NewFN As Variant
    ' COPY INVOICE TO A NEW WORKBOOK
    NewFN = "C:\Users\pj_pr\Desktop\Peachy Pools\Invoices\InvPP" & Range("K9").Value
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\pj_pr\Desktop\Peachy Pools\Invoices\InvPP" & Range("K9").Value _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False

    'Create an Outlook object and new mail message
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItem(0)

    'Display email and specify To, Subject, etc
    With OutlookMail

    .Display
    .To = Email_To
    .CC = Email_CC
    .BCC = Email_BCC
    .Subject = EmailSubject
    .Attachments.Add = Filename:="C:\Users\pj_pr\Desktop\Peachy Pools\Invoices\InvPP" & Range("K9").Value

    If DisplayEmail = False Then

    .Send

    End If

    End With
    ActiveSheet.PrintOut , Copies:=1
    Range("k9").Value = Range("k9").Value + 1
    Range("b18:h32").ClearContents
    Range("j18:j32").ClearContents
    Range("j10:k10").ClearContents
    ActiveWorkbook.Save

    End Sub

  2. #2
    Valued Forum Contributor meabrams's Avatar
    Join Date
    10-03-2014
    Location
    USA
    MS-Off Ver
    2007, 2010, 2013, 2016
    Posts
    451

    Re: vba code to auto print save and email client

    Please Remember to use code tags around any macros you post..!

    In your code change this line
    Please Login or Register  to view this content.
    To
    Please Login or Register  to view this content.
    Bramz

  3. #3
    Registered User
    Join Date
    09-04-2017
    Location
    Gold Coast, Australia
    MS-Off Ver
    Home & Student 2016
    Posts
    2

    Re: vba code to auto print save and email client

    your a lifesaver, thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA Code to Auto intialsend email & reminder email based on cell value
    By swk8429 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-16-2015, 06:15 AM
  2. Auto Save-as & Send Email Notification
    By Nerdy Nurse in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-18-2014, 01:08 PM
  3. Client code for differentiated client price
    By a1b2c3d4e5f6g7h8i9 in forum Excel General
    Replies: 5
    Last Post: 02-27-2014, 12:14 PM
  4. Save as PDF and email / User defined Sheets and Print Areas
    By alexq in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-16-2010, 04:19 PM
  5. Sending Email Dependent on Default Email Client
    By Julesdude in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-30-2010, 06:51 AM
  6. Outlook, print pdf Save Attachment, Move Email To A Subfolder
    By stephen1000 in forum Outlook Programming / VBA / Macros
    Replies: 7
    Last Post: 11-16-2009, 07:18 PM
  7. Sending email using users preferred email client
    By deadlyduck in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-29-2009, 06:29 AM

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