Outlook Macro does run properly when sending excel attachment

[whosphil]

Hello all,

I created a userform for Outlook that populates an email based on what the user chooses and enters in userform1. This works fine when I create a new email, but when I choose an Excel File or any type of file and send to mail recipient the combobox draws a blank in userform1. How do I fix this?

Here's the code for userform1.

Option ExplicitPublic AlbanyLoad As Integer, CanastotaLoad As Integer, ChicopeeLoad As Integer, ColonieLoad As Integer
Public Body As String
Public ShipDate As Date






Private Sub LocationBox_Change()
Select Case LocationBox.Value
    Case "Albany"
        AlbanyLabel.Visible = True
        AlbanyLoadbox.Visible = True
        CanastotaLAbel.Visible = True
        CanastotaLoadBox.Visible = True
        ChicopeeLabel.Visible = True
        ChicopeeLoadBox.Visible = True
        ColonieLabel.Visible = True
        ColonieLoadBox.Visible = True
        ShipDateLabel.Visible = True
        ShipDateBox.Visible = True
    Case "Canastota"
        AlbanyLabel.Visible = False
        AlbanyLoadbox.Visible = False
        CanastotaLAbel.Visible = True
        CanastotaLoadBox.Visible = True
        ChicopeeLabel.Visible = False
        ChicopeeLoadBox.Visible = False
        ColonieLabel.Visible = False
        ColonieLoadBox.Visible = False
        ShipDateLabel.Visible = True
        ShipDateBox.Visible = True
    Case "Chicopee"
        AlbanyLabel.Visible = False
        AlbanyLoadbox.Visible = False
        CanastotaLAbel.Visible = False
        CanastotaLoadBox.Visible = False
        ChicopeeLabel.Visible = True
        ChicopeeLoadBox.Visible = True
        ColonieLabel.Visible = False
        ColonieLoadBox.Visible = False
        ShipDateLabel.Visible = True
        ShipDateBox.Visible = True
    Case "Colonie"
        AlbanyLabel.Visible = False
        AlbanyLoadbox.Visible = False
        CanastotaLAbel.Visible = False
        CanastotaLoadBox.Visible = False
        ChicopeeLabel.Visible = False
        ChicopeeLoadBox.Visible = False
        ColonieLabel.Visible = True
        ColonieLoadBox.Visible = True
        ShipDateLabel.Visible = True
        ShipDateBox.Visible = True
End Select
        
End Sub


Private Sub UserForm_Initialize()




With LocationBox
.AddItem "Chicopee"
.AddItem "Albany"
.AddItem "Colonie"
.AddItem "Canastota"
End With
End Sub




Private Sub CommandButton1_Click()
Dim oMail As Outlook.MailItem


ShipDate = Format(ShipDateBox.Value, "MM/DD/YY")


Body = "has been uploaded."


Set oMail = ActiveInspector.CurrentItem


Select Case LocationBox.Value


    Case "Albany"
        With oMail
            .To = "Albany Report"
            .Subject = "Albany " & ShipDate & " " & WeekdayName(Weekday(ShipDate)) & " Ship Uploaded"
            .Body = AlbanyLoadbox.Value & " Albany Loads" & vbLf & CanastotaLoadBox.Value & " Canastota Loads" & vbLf & ChicopeeLoadBox.Value & " Chicopee Loads" & vbLf & ColonieLoadBox.Value & " Colonie Loads" & vbLf & vbLf & "Phil"
            End With
    Case "Canastota"
        With oMail
            .To = "Canastota Report"
            .Subject = "Canastota " & ShipDate & " " & WeekdayName(Weekday(ShipDate)) & " Ship Uploaded"
            .Body = CanastotaLoadBox.Value & " Canastota Loads" & vbLf & vbLf & "Phil"
            End With
    Case "Chicopee"
        With oMail
            .To = "Chicopee Report"
            .Subject = "Chicopee " & ShipDate & " " & WeekdayName(Weekday(ShipDate)) & " Ship Uploaded"
            .Body = ChicopeeLoadBox.Value & " Chicopee Loads" & vbLf & vbLf & "Phil"
            End With
    Case "Colonie"
        With oMail
            .To = "Colonie Report"
            .Subject = "Colonie " & ShipDate & " " & WeekdayName(Weekday(ShipDate)) & " Ship Uploaded"
            .Body = ColonieLoadBox.Value & " Colonie Loads" & vbLf & vbLf & "Phil"
            End With
    End Select


Unload Me
oMail.Send




End Sub

and my module code..

Public Sub ReportEmails()UserForm1.Show
End Sub

Please let me know if you need more information. Thanks!

 

[whosphil]

Does anyone know how to solve this problem??