PDA

View Full Version : [SOLVED] Excel to Email with Attachment, based on different names & then folders



DarinM
06-02-2015, 07:46 AM
Hi all,

I have figured out (with the great help of Kenneth), how to send an email with an attachment based on criteria in an excel, and only send that e-mail to the line you are clicked on.

However, I have made my tracking sheet more complex than originally thought, and now I want to try and make my attachment macro fit that bill.

Original thread is here
http://www.vbaexpress.com/forum/showthread.php?52442-Excel-to-Email-with-Attachment-(1-line-only-at-a-time-with-button-push)

So, I will try and explain my current issue.
Column C has a dropdown list with 5 names in it.


Tom
Nicola
Annie

Column A has a formula in it that is based off Column C and Column O, that hyperlinks the contents in Column O with the directory based on the name in Column C


=IF(C15="Tom",HYPERLINK("\\HAUCACAM0007VS\Shares\Marketing_bridge\Darin\`MasterTracker\EventRequestF orms\Tom\"&O15,"Link to PDF")

So right now, C15=Tom or Annie or Nicola, the folder changes \Tom\ or \Annie\ or \Nicola\.

I need that to happen for my attachment portion of my code, however I am not sure how it can check for a name in the active row (remember this email is only being sent out based on what line you are in).

Ideally the code would be something like If(C15="Tom", point attachment directory to \Tom\ folder, & Cells(ActiveCell.Row, 15).Value2 & ""

Not sure if this is possible, but if it is...then this is the last piece to my puzzle !!


Sub SendReminderForNewClinic()' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010.
' This example sends the last saved version of the Activeworkbook object .
Dim OutApp As Object
Dim OutMail As Object


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


On Error Resume Next
' Change the mail address and subject in the macro before you run it.
With OutMail
.To = Cells(ActiveCell.Row, 16)
.CC = Cells(ActiveCell.Row, 17)
.BCC = ""
.Subject = Cells(ActiveCell.Row, 18)
.Body = Cells(ActiveCell.Row, 19)
.Attachments.Add "\\HAUCACAM0007VS\Shares\Marketing_bridge\Darin\`MasterTracker\NewClinicOnBo ard_Files\" & Cells(ActiveCell.Row, 15).Value2 & ""
' You can add other files by uncommenting the following line.
'.Attachments.Add ("C:\test.txt")
' In place of the following statement, you can use ".Display" to
.Display
'.Send
End With
On Error GoTo 0


Set OutMail = Nothing
Set OutApp = Nothing
End Sub


Appreciate suggestions/help and comments!

Darin.

DarinM
06-02-2015, 07:50 AM
wow typing that out in logic just made me solve it.

I just added a piece of code before & cells(activecell, and pointed it to the name, then added a & "\" & piece.

see below


.Attachments.Add "\\HAUCACAM0007VS\Shares\Marketing_bridge\Darin\`MasterTracker\NewClinicOnBo ard_Files\" & Cells(ActiveCell.Row, 3) & "\" & Cells(ActiveCell.Row, 15).Value2 & ""


Sub SendReminderForNewClinic()' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010.
' This example sends the last saved version of the Activeworkbook object .
Dim OutApp As Object
Dim OutMail As Object


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


On Error Resume Next
' Change the mail address and subject in the macro before you run it.
With OutMail
.To = Cells(ActiveCell.Row, 16)
.CC = Cells(ActiveCell.Row, 17)
.BCC = ""
.Subject = Cells(ActiveCell.Row, 18)
.Body = Cells(ActiveCell.Row, 19)
.Attachments.Add "\\HAUCACAM0007VS\Shares\Marketing_bridge\Darin\`MasterTracker\NewClinicOnBo ard_Files\" & Cells(ActiveCell.Row, 3) & "\" & Cells(ActiveCell.Row, 15).Value2 & ""
' You can add other files by uncommenting the following line.
'.Attachments.Add ("C:\test.txt")
' In place of the following statement, you can use ".Display" to
.Display
'.Send
End With
On Error GoTo 0


Set OutMail = Nothing
Set OutApp = Nothing
End Sub


Private Sub CommandButton1_Click()


End Sub