Using If Then Else for Email Attachments

chenaujr

New Member
Joined
Jun 30, 2014
Messages
2
Hey Everyone,
I'm brand new to VBA, and I think I've exhausted my efforts in trying to figure out my problem. Our VBA expert left the company, and I was tasked with optimizing a tool that he created which we use to send email correspondences to candidates. The code works as is, but my manager wants specific documents to attach to their email depending on the candidate's role, location, and whether or not they're flying to our location. I refrained from adding the whole code since it's about 6 pages or so. Any suggestions are greatly appreciated, but I am 100% stuck right now. Keep in mind that since this tool is actually used for multiple purposes, not just for populating emails, I cannot shift around columns, rows, etc. It has to stay as is for other reporting.

'=== Create Email From Reference Files and Properties Above ===
Dim newDoc As Object
Set newDoc = wdApp.Documents.Add(strTemplateFile)​

Call wdApp.Run("Replace", strCanFirstName, strCanLastName, strCanPhone, strCanEmail, strCanPosition, strIntDate, strIntTime, strIntLocation, strIntAddress, strRecName, strRecPhone, strRecEmail, strHotelName, strHotelAddress, strCarlsonTravel, lngRecordRow)​

Call wdApp.Run("DeleteParagraphs", bReception, bHotel, bCarlsonTravel, lngRecordRow)​

Call wdApp.Run("CreateEmail", strCanEmail, strAttachment1, lngRecordRow)​

newDoc.Close SaveChanges:=wddonotsavechanges
'=============================================================

Really hope that's making sense, but that is the code currently wich works fine. So before the line third call, I need something along the lines of...

If UCase(strCanPosition) = "Finance" And UCase(strIntCity) = "Richmond" And UCase(bAirTravel = True) Then

Call wdApp.Run("CreateEmail", strCanEamil, strAttachment1, strAttachment2, lngRecordRow)

' That would be the first case, but it would then need to cycle others, and if none of the cases were true, it would use the statement that creates an email with just strAttachment1.

I feel like this is all convoluted, but if you need more clarity or insight let me know. Again, a huge thanks to any suggestions or insights as to what I could be doing wrong.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

chenaujr

New Member
Joined
Jun 30, 2014
Messages
2
Realized I committed a big No-No by not reading the posting guidelines, so I wanted to add a few things.

I am using a PC running Windows 7, with Office 2010. For easier viewing, I also added the HTML plug in, so the current working code for Creating the Email is below. The code in Excel communicates with Word, which also has a code, inputs additional information, then copies and pastes to the body of the Outlook message. We have to use this method due to images and specific formatting required in the email.

[FONT=Courier]
[COLOR=#007F00]'=== Create Email From Reference Files and Properties Above ===[/COLOR]
[COLOR=#00007F]Dim[/COLOR] newDoc [COLOR=#00007F]As[/COLOR] [COLOR=#00007F]Object[/COLOR]
[COLOR=#00007F]Set[/COLOR] newDoc = wdApp.Documents.Add(strTemplateFile)

[COLOR=#00007F]Call[/COLOR] wdApp.Run("Replace", strCanFirstName, strCanLastName, strCanPhone, strCanEmail, strCanPosition, strIntDate, strIntTime, strIntLocation, strIntAddress, strRecName, strRecPhone, strRecEmail, strHotelName, strHotelAddress, strCarlsonTravel, lngRecordRow)
[COLOR=#00007F]Call[/COLOR] wdApp.Run("DeleteParagraphs", bReception, bHotel, bCarlsonTravel, lngRecordRow)
[COLOR=#00007F]Call[/COLOR] wdApp.Run("CreateEmail", strCanEmail, strAttachment1, strAttachment2, lngRecordRow)

newDoc.Close SaveChanges:=wddonotsavechanges
[COLOR=#007F00]'=============================================================[/COLOR]

[COLOR=#00007F]Exit[/COLOR] [COLOR=#00007F]Sub[/COLOR]

SmoothError:
MsgBox "An error has occured for the record on row " & lngRecordRow & "! This record will not be processed."

[COLOR=#00007F]End[/COLOR] [COLOR=#00007F]Sub[/COLOR]
[/FONT]
 

Forum statistics

Threads
1,136,317
Messages
5,675,042
Members
419,546
Latest member
RobWayCot

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