Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error handling for missing Outlook attachments
Hi, I have an Excel macro that creates a custom email in Outlook - uses
recipients' first and last names in the body of the message, provides recipient-specific data, and attaches files for that particular person. All of the data in the Outlook email is retrieved from an Excel spreadsheet. When the directory path and/or file name for one or more attachments is misspelled in the Excel spreadsheet (for example - file is spelled as C:\ FilName.doc instead of C:\FileName.doc) and the macro can't find the file, it just ignores it, and the email is sent without the attachment(s). I would like to install an error handling routine that can identify when an attachment is not found, will create a folder called AttachmentErrors in my Outlook Mailbox, and send the problem email to this, instead of sending the email to the recipient. Can someone help with this? A copy of the code is included below. Note: I have never done any VBA programming before, but am an experienced programmer in a number of other languages, so code is probably a little rough. :-) Also, I have included extensive comments because this application will be distributed to several employees who are not technically oriented at all - any suggestions to make code easier for them would be appreciated. Debbie ------------------------- Sub SendEmail() ' Define Outlook Application variables Dim bStarted As Boolean Dim oApp As Outlook.Application Dim oMailItem As MailItem Dim oAttach As Attachments ' Define placeholder variables Dim Msg As String 'needed to compose message body ' Set up row counters Dim r As Integer 'row counter ' __________________________________________________ ___________________ ' Check to see if Outlook is running. If not, start Outlook On Error Resume Next Set oOutlookApp = GetObject(, "Outlook.Application") If Err < 0 Then Set oOutlookApp = CreateObject("Outlook.Application") bStarted = True End If '_________________________________________________ _____________________ ' Note: The variable values are located as follows: ' FirstName - Column 1 = Cell(r,1) ' LastName - Column 2 = Cell(r,2) ' Email - Column 3 = Cell(r,3) ' Phone - Column 4 = Cell(r,4) ' Extension - Column 5 = Cell(r,5) ' Subject - Column 6 = Cell(r,6) ' Attach1 - Column 7 = Cell(r,7) ' Attach2 - Column 8 = Cell(r,8) ' Attach3 - Column 9 = Cell(r,9) ' Attach4 - Column 10 = Cell(r,10) ' Attach5 - Column 11 = Cell(r,11) ' Select Rows For r = 2 To 9 'Use data from Rows 2 through 9 in Excel spreadsheet ' Compose the message ' Note: The "vbCrLf" code inserts a carriage return or line break ' The ampersand symbol (&) is used for concatenation Msg = "" 'This line clears out the old message contents. Keep this! Msg = "This is a test message. Please forward it back to Debbie and delete. Thanks!" Msg = Msg & vbCrLf & "_________________________________________________ ____" & vbCrLf ' Dear FirstName LastName Msg = Msg & "Dear " & Cells(r, 1) & " " & Cells(r, 2) & "," & vbCrLf & vbCrLf Msg = Msg & "Here is your contact information" & vbCrLf & vbCrLf ' Email address Msg = Msg & "Email:" & " " & Cells(r, 3).Text & vbCrLf ' Phone Number Msg = Msg & "Phone:" & " " & Cells(r, 4) & vbCrLf ' Extension Msg = Msg & "Extension:" & " " & Cells(r, 5) & vbCrLf Set oApp = New Outlook.Application ' Create Mail Item Set oMailItem = oApp.CreateItem(olMailItem) 'Create a new Outlook email oMailItem.Save oMailItem.Recipients.Add Cells(r, 3) 'Identify Email Recipient oMailItem.Subject = Cells(r, 6) 'Create Subject line oMailItem.Body = Msg 'Create Body of message using Msg template Set oAttach = oMailItem.Attachments oAttach.Add Trim(Cells(r, 7).Value), olByValue, 1 'Include Attach1 oAttach.Add Trim(Cells(r, 8).Value), olByValue, 1 'Include Attach2 oAttach.Add Trim(Cells(r, 9).Value), olByValue, 1 'Include Attach3 oAttach.Add Trim(Cells(r, 10).Value), olByValue, 1 'Include Attach4 oAttach.Add Trim(Cells(r, 11).Value), olByValue, 1 'Include Attach5 oMailItem.Send 'Send email Set oAttach = Nothing 'Clear attachments Set oMailItem = Nothing 'Clear current email content oApp.Quit 'Quit this occurrence of Outlook Set oApp = Nothing 'Clear Outlook application Next r End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error handling for missing Outlook attachments
Hi cassels1
You can test if the file exist with Dir I use it in this example http://www.rondebruin.nl/mail/folder2/files.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "cassels1" <u47127@uwe wrote in message news:8c19e1306caea@uwe... Hi, I have an Excel macro that creates a custom email in Outlook - uses recipients' first and last names in the body of the message, provides recipient-specific data, and attaches files for that particular person. All of the data in the Outlook email is retrieved from an Excel spreadsheet. When the directory path and/or file name for one or more attachments is misspelled in the Excel spreadsheet (for example - file is spelled as C:\ FilName.doc instead of C:\FileName.doc) and the macro can't find the file, it just ignores it, and the email is sent without the attachment(s). I would like to install an error handling routine that can identify when an attachment is not found, will create a folder called AttachmentErrors in my Outlook Mailbox, and send the problem email to this, instead of sending the email to the recipient. Can someone help with this? A copy of the code is included below. Note: I have never done any VBA programming before, but am an experienced programmer in a number of other languages, so code is probably a little rough. :-) Also, I have included extensive comments because this application will be distributed to several employees who are not technically oriented at all - any suggestions to make code easier for them would be appreciated. Debbie ------------------------- Sub SendEmail() ' Define Outlook Application variables Dim bStarted As Boolean Dim oApp As Outlook.Application Dim oMailItem As MailItem Dim oAttach As Attachments ' Define placeholder variables Dim Msg As String 'needed to compose message body ' Set up row counters Dim r As Integer 'row counter ' __________________________________________________ ___________________ ' Check to see if Outlook is running. If not, start Outlook On Error Resume Next Set oOutlookApp = GetObject(, "Outlook.Application") If Err < 0 Then Set oOutlookApp = CreateObject("Outlook.Application") bStarted = True End If '_________________________________________________ _____________________ ' Note: The variable values are located as follows: ' FirstName - Column 1 = Cell(r,1) ' LastName - Column 2 = Cell(r,2) ' Email - Column 3 = Cell(r,3) ' Phone - Column 4 = Cell(r,4) ' Extension - Column 5 = Cell(r,5) ' Subject - Column 6 = Cell(r,6) ' Attach1 - Column 7 = Cell(r,7) ' Attach2 - Column 8 = Cell(r,8) ' Attach3 - Column 9 = Cell(r,9) ' Attach4 - Column 10 = Cell(r,10) ' Attach5 - Column 11 = Cell(r,11) ' Select Rows For r = 2 To 9 'Use data from Rows 2 through 9 in Excel spreadsheet ' Compose the message ' Note: The "vbCrLf" code inserts a carriage return or line break ' The ampersand symbol (&) is used for concatenation Msg = "" 'This line clears out the old message contents. Keep this! Msg = "This is a test message. Please forward it back to Debbie and delete. Thanks!" Msg = Msg & vbCrLf & "_________________________________________________ ____" & vbCrLf ' Dear FirstName LastName Msg = Msg & "Dear " & Cells(r, 1) & " " & Cells(r, 2) & "," & vbCrLf & vbCrLf Msg = Msg & "Here is your contact information" & vbCrLf & vbCrLf ' Email address Msg = Msg & "Email:" & " " & Cells(r, 3).Text & vbCrLf ' Phone Number Msg = Msg & "Phone:" & " " & Cells(r, 4) & vbCrLf ' Extension Msg = Msg & "Extension:" & " " & Cells(r, 5) & vbCrLf Set oApp = New Outlook.Application ' Create Mail Item Set oMailItem = oApp.CreateItem(olMailItem) 'Create a new Outlook email oMailItem.Save oMailItem.Recipients.Add Cells(r, 3) 'Identify Email Recipient oMailItem.Subject = Cells(r, 6) 'Create Subject line oMailItem.Body = Msg 'Create Body of message using Msg template Set oAttach = oMailItem.Attachments oAttach.Add Trim(Cells(r, 7).Value), olByValue, 1 'Include Attach1 oAttach.Add Trim(Cells(r, 8).Value), olByValue, 1 'Include Attach2 oAttach.Add Trim(Cells(r, 9).Value), olByValue, 1 'Include Attach3 oAttach.Add Trim(Cells(r, 10).Value), olByValue, 1 'Include Attach4 oAttach.Add Trim(Cells(r, 11).Value), olByValue, 1 'Include Attach5 oMailItem.Send 'Send email Set oAttach = Nothing 'Clear attachments Set oMailItem = Nothing 'Clear current email content oApp.Quit 'Quit this occurrence of Outlook Set oApp = Nothing 'Clear Outlook application Next r End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Outlook attachments | Excel Programming | |||
Outlook attachments | Excel Programming | |||
opening zip outlook attachments | Excel Programming | |||
saving Outlook attachments | Excel Programming | |||
Error Handling Open Function or query for missing Files | Excel Programming |