VBA to conditionally email via outlook based on filename?

[mjd]

Hello all.

I'm trying to get some straight-through-processing set up for a bunch of EOD reports my team sends to various banks. As it stands, I have some great code that will automatically save a file based off the bank the data pertains to as well as the date of the information. I also have some great code that will automatically send these files to the recipients.

Now here is the rub.

If the data we're sending is from a prior date the naming convention changes. In these situations, we want to save the email as a draft so we can include specific notes that cannot be pre-programmed. I can manipulate the code to do this, but ideally there would be a conditional statement that interprets the send outlook email vs. save outlook as draft based on the file's name.

Here is the code I have that will do the conditional save:

Sub save()Dim dtDate As Date
Dim strFile As String
Dim strPath As String


    strPath = "U:\Test\"


    dtDate = Date


    If dtDate = Range("B3").Value Then
        strFile = Format(dtDate, "mm-dd") & " Bank.xlsx"
    Else
        strFile = Format(Range("B3").Value, "mm-dd") & " As Of DATA - Bank.xlsx"
    End If


    ActiveWorkbook.SaveAs Filename:=strPath & strFile, FileFormat _
    :=xlOpenXMLWorkbook, CreateBackup:=False


    Application.DisplayAlerts = Fals
End Sub

So any data from the current day is saved as "05-27 Bank.xlsx", and if the file was yesterday's data it would save as "05-26 As of DATA - Bank.xlsx"


Here is the code I'm using for the outlook functions:

Dim OutApp As Object
    Dim OutMail As Object


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)


    On Error Resume Next
   ' Change the mail address and subject in the macro before you run it.
    With OutMail
        .SentOnBehalfOfName = "information@some company.com"
        .To = "recipient@another company.com"
        .CC = ""
        .BCC = ""
        .Subject = "Data:" & " " & ActiveWorkbook.Name
        .Body = "Please see attached for details." & vbNewLine & vbNewLine & "Thanks"
        .Attachments.Add ActiveWorkbook.FullName
              ' In place of the following statement, you can use ".Display" to display the mail.
              ' To save as a draft use ".Save" & ".Close olPromptForSave"
        .Send
    End With
    On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing

Is it possible to file name to conditionally pick the outlook function to use?

Thank you for your time and help!

Mike

 

[BarryL]

Hi Mike, try the below

Dim OutApp As Object
    Dim OutMail As Object


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)


    On Error Resume Next
   ' Change the mail address and subject in the macro before you run it.
    With OutMail
        .SentOnBehalfOfName = "information@some company.com"
        .To = "recipient@another company.com"
        .CC = ""
        .BCC = ""
        .Subject = "Data:" & " " & ActiveWorkbook.Name
        .Body = "Please see attached for details." & vbNewLine & vbNewLine & "Thanks"
        .Attachments.Add ActiveWorkbook.FullName
              ' In place of the following statement, you can use ".Display" to display the mail.
              ' To save as a draft use ".Save" & ".Close olPromptForSave"
           If Left(ActiveWorkbook.Name, 5) = Format(Date, "mm-dd") Then
           .Send
            Else
        .Save
        End If
    End With
    On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing

 

[mjd]

thank you, Barry! Thank worked perfectly!