Case Question

daily106

Board Regular
Joined
Dec 20, 2004
Messages
158
Hello, All.

I would like to know what I am doing wrong for the case formular. I am trying to move emails to folders according to the sender's email addresses. I am have a problem where case function is stated...



Sub Billing()

Dim ns As NameSpace
Dim Inbox As MAPIFolder
Dim Item As Object
Dim currentMailItem As MailItem
Dim senderemailaddress As MailItem
Dim atmt As Attachment
Dim FileName As String
Dim i As Integer
Dim Subfolder As MAPIFolder
Dim VarResponse As VbMsgBoxResult


Set ns = GetNamespace("MAPI")
Set Inbox = ns.GetDefaultFolder(olFolderInbox)
i = 0
ii = i - 1
Set Subfolder = Inbox.Folders("Billing")
Set Item = Subfolder.Items




If Subfolder.UnReadItemCount = 0 Then

MsgBox "There is No New Fax Materials in Billing Folder", vbInformation, " No Invoice "

Exit Sub


End If


If Subfolder.UnReadItemCount > 0 Then

For Each Item In Subfolder.Items

For Each atmt In Item.Attachments

' THE PROBLEM IS THAT Case Item.senderemailaddress DOES NOT WORK.

Select Case Item.senderemailaddress

Case Item.senderemailaddress = "aaa@aaa.com"
FileName = "T:\Carriers\Carrier Invoice\aaa\" '& "aaa " & Format(Item.ReceivedTime, "yyyymmdd_hhnn_") & atmt.FileName
Case Item.senderemailaddress = "bbb@bbb.com"
FileName = "T:\Carriers\Carrier Invoice\bbb\" '& "bbb " & Format(Item.ReceivedTime, "yyyymmdd_hhnn_") & atmt.FileName

Case Else

FileName = "T:\Carriers\Carrier Invoice\" & Format(Item.ReceivedTime, "yyyymmdd_hhnn_") & atmt.FileName

End Select



atmt.SaveAsFile FileName

i = i + 1

Next atmt

Item.UnRead = False

Next Item

End If







If i > 0 Then
VarResponse = MsgBox(i & " attached files found!." _
& vbCrLf & "The files have been saved in T:\Carriers\Carrier Invoice\" _
& vbCrLf & "Would you like to view the files now?" _
, vbQuestion + vbYesNo, "Finished!")

If VarResponse = vbYes Then
Shell "Explorer.exe , T:\Carriers\Carrier Invoice\", vbNormalFocus

End If

Else
MsgBox "No new attachments found.", vbInformation, _
"Finished!"

End If




MoveEmailAttachments_exit:

Set ns = Nothing
Set atmt = Nothing
Set Inbox = Nothing
Set Subfolder = Nothing
Set Item = Nothing
Set currentMailItem = Nothing

Exit Sub

MoveEmailAttachments_Err:

MsgBox "An unexpected error has occurred." _
& vbCrLf & "Please note and report the following information." _
& vbCrLf & "Macro Name: GetAttachments" _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description: " & Err.Description _
, vbCritical, "Error!"
Resume MoveEmailAttachments_exit:

End Sub


Please let me know what I am doing wrong. Thank you all for your time!!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Remove Item.senderemailaddress = from the Case statements.
Code:
Select Case Item.senderemailaddress
    Case "aaa@aaa.com"
        Filename = "T:\Carriers\Carrier Invoice\aaa" '& "aaa " & Format(Item.ReceivedTime, "yyyymmdd_hhnn_") & atmt.FileName
    Case "bbb@bbb.com"
        Filename = "T:\Carriers\Carrier Invoice\bbb" '& "bbb " & Format(Item.ReceivedTime, "yyyymmdd_hhnn_") & atmt.FileName
    Case Else
        Filename = "T:\Carriers\Carrier Invoice" & Format(Item.ReceivedTime, "yyyymmdd_hhnn_") & atmt.Filename
End Select
 

daily106

Board Regular
Joined
Dec 20, 2004
Messages
158
SOLVED

Thank you very much, Norie!!!

It works great...Never thought Case statement would make my life so much easier.

Again, thanks for your time!
 

Forum statistics

Threads
1,136,272
Messages
5,674,755
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top