CODEWITHSUNDEEP
excelvbaif-statement
Joe
Joe

Reputation: 5

VBA Excel If Statement based on Text Box Entry

I am trying to create a macro that will enable someone to type in a file name and then an email list number that will select a specific distro list. I've combined a lot of different codes online to create this. If I were to just place an email into the ".to = " it works correctly. But I want to implement the if statement based on which list# the person would want to send the workbook too. When I run the macro it won't send anything, which I am assuming my if statement is not working correctly. Any advice? 

Sub Mail_workbook_Test()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim Date1 As Date

    Date1 = Format(Now, "yyyy-mm-dd")
    'Date and format
    Filename = Application.InputBox("Enter File Name:", "Input Box Text", "File Name")
    'Type in File Name
    List = Application.InputBox("Enter Email List:", "Input Box Text", "List#")
    'Type in Email List
    If List = "List1" Then
    emailaddress = "[email protected]; [email protected]"
    ElseIf List = "List2" Then
    emailaddress = "[email protected]; [email protected]"
    End If


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

    On Error Resume Next
    With OutMail
        .to = emailaddress
        .CC = ""
        .BCC = ""
        .Subject = "" + Filename + "" & " " & Date1
        .Body = "Hi Everyone," & Chr(10) & Chr(10) & "Please let me know if you get this!" & Chr(10) & Chr(10) & "Thanks!"""
        .Attachments.Add ("C:\Users\Desktop\" + Filename + ".xlsx")
        .Send   'or use .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Upvotes: 0

Views: 2141

Answers (2)

David Zemens
David Zemens

Reputation: 53623

A few housekeeping items that I will comment on but not correct in your code:

  1. Get rid of On Error Resume Next, that is potentially preventing you from seeing an error condition that might be the root cause of the failure.
  2. Also, you should consider using a FileDialog instead of an inputbox to choose a file. This mitigates the (very likely) chance of user error in the inputbox.

I believe, but this is not tested, that the .To property deals with the displayname, and according to the documentation this is the case:

This property contains the display names only. The To property corresponds to the MAPI property PidTagDisplayTo. The Recipients collection should be used to modify this property.

So you could try this instead:

Dim recipient as Variant
With OutMail
    For each recipient in Split(emailaddress, ";")
        .Recipients.Add Trim(recipient)
    Next
    .CC = ""
    .BCC = ""
    .Subject = "" + Filename + "" & " " & Date1
    .Body = "Hi Everyone," & Chr(10) & Chr(10) & "Please let me know if you get this!" & Chr(10) & Chr(10) & "Thanks!"""
    .Attachments.Add ("C:\Users\Desktop\" + Filename + ".xlsx")
    .Send   'or use .Display
End With

Upvotes: 1

David
David

Reputation: 1232

I'm unable to test as I don't have Outlook on my home machine, but if you suspect that it's your If routine where it's failing then I suggest adding an extra statement to catch a bad user entry. Here's the full If statement with an extra final 'Else' (meaning "if none of the previous conditions were met then do this"):

If List = "List1" Then
    emailaddress = "[email protected]; [email protected]"
ElseIf List = "List2" Then
    emailaddress = "[email protected]; [email protected]"
Else
    MsgBox "Sorry, your list selection was not recognised."
    Exit Sub
End If

This will guarantee that your 'emailaddress' variable gets set correctly, OR you get a useful error message. Hope this is some help.

Upvotes: 1

Related Questions