Tracking/recording date auto-email was sent via Macro

[cdg786]

This may not be possible, but has anyone found a way to track and record each instance of running an auto-pdf-email macro in excel? I have built a system to automatically send purchase orders based on vendor information. Ideally, I can automatically record the most recent issuance date (send date) each time we send an updated purchase order. I would want to do this by vendor, assigning that record to a vendor summary page. Currently I have an order form template sheet created. The template includes a field that, when changed, adjusted the form to include the assigned vendor info. Then you just hit the send botton.

Might be too much for excel. Figured I would send this into the void and see what happens.

 

[Candyman8019]

without seeing your actual sheets and code that sends the email, it's difficult to provide direction. That being said, you'll be looking at something along the lines of:

'In this example, your code just sent an email to Vendor 10.
' assume i is set to the row of Vendor 10 on your vendor summary page and
' Vendor name is in column A | Last email date is column B

Sheet1.Range("B" & i).Value = "Email sent " & Now

 

[cdg786]

Thanks -- Good point. See below vba code.

Public Sub Save_Range_As_PDF_and_Send_Email()

    Dim PDFrange As Range
    Dim PDFfile As String
    Dim toEmail As String, emailSubject As String
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim HTMLBody As String
    
    With ActiveWorkbook
    
        Set PDFrange = .ActiveSheet.Range("A1:I37")
        toEmail = .ActiveSheet.Range("B15").Value
        emailSubject = .ActiveSheet.Range("C6").Value
  
        PDFfile = Replace(.FullName, ".xlsx", ".pdf")
    
    End With
    
    PDFrange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

    'Send email with PDF file attached

    Set OutApp = New Outlook.Application

    Set OutMail = OutApp.CreateItem(olMailItem)
    With OutMail
        .Display
        .To = toEmail
        .Subject = emailSubject
        .HTMLBody = "<p> Hello, </p>" & "<p> Please see attached bid request. We are happy to connect to discuss an missing details. </p>" & _
                    "<p> Thank you, </p>" & "<p> </p>" & .HTMLBody
                            
        .Attachments.Add PDFfile
        .send
    End With

    'Delete the temporary PDF file

    Kill PDFfile

    Set OutMail = Nothing

End Sub

 

[Candyman8019]

I don't know what your vendor sheet looks like, but essentially, pick a cell to record the send date and then update it in the code below:

Public Sub Save_Range_As_PDF_and_Send_Email()

    Dim PDFrange As Range
    Dim PDFfile As String
    Dim toEmail As String, emailSubject As String
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim HTMLBody As String
    
    With ActiveWorkbook
    
        Set PDFrange = .ActiveSheet.Range("A1:I37")
        toEmail = .ActiveSheet.Range("B15").Value
        emailSubject = .ActiveSheet.Range("C6").Value
  
        PDFfile = Replace(.FullName, ".xlsx", ".pdf")
    
    End With
    
    PDFrange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

    'Send email with PDF file attached

    Set OutApp = New Outlook.Application

    Set OutMail = OutApp.CreateItem(olMailItem)
    With OutMail
        .Display
        .To = toEmail
        .Subject = emailSubject
        .HTMLBody = "<p> Hello, </p>" & "<p> Please see attached bid request. We are happy to connect to discuss an missing details. </p>" & _
                    "<p> Thank you, </p>" & "<p> </p>" & .HTMLBody
                            
        .Attachments.Add PDFfile
        .send
    End With

    'Delete the temporary PDF file

    Kill PDFfile

    Set OutMail = Nothing
'Change C15 below to wherever you want the last send date to be recorded.
Activesheet.Range("C15").Value = "Email sent " & Now
End Sub

 

[cdg786]

Thanks. This seems to work. How would I set it up to post the send date based on which PO is sent? I may have a list of 40 different vendors, each with a separate PO being sent through this macro. Ideally, the PO Sent Date is listed in the corresponding vendor row on a separate (still in same workbook) summary page. I imagine it is some sort of ifs statement?

 

[Candyman8019]

Are you able to share your sheet with OneDrive or Dropbox (with data sanitized as needed)...it will be much easier to understand what exactly you're trying to do.