Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Email specific sheets
I'm sure I saw this done a year or so ago, when I was looking something
else up, but naturally can't find it now. I'm trying to set up an automated distribution list, triggered by a command button. I know it can be done with the code: ActiveWorkbook.SendMail ", ") etc .... but I want different sheets in the workbook to go to different people. Therefore I'd set up a list with: A B C D dave@... sheet1 sheet4 sheet 5 bob@... sheet1 sheet2 sheet3 derek@... sheet4 sheet5 sheet6 Is there a way to have a macro look down column A and then send the listed sheets to the appropriate address? I'm assuming that there'd be a VLookup equivalent needed..? Thanks in advance Steve |
#2
|
|||
|
|||
Steve,
Here is some code to get you started. The SendMail didn't work for me, but as I never use SendMail, I have no idea why. Perhaps you will. It just picks up the address from column A Sub SendSheets() Dim thisSheet As Worksheet Dim cLastRow As Long Dim clastcol As Long Dim arySheets Dim i As Long, j As Long cLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row Set thisSheet = ActiveSheet For i = 1 To cLastRow clastcol = thisSheet.Cells(i, Columns.Count).End(xlToLeft).Column On Error Resume Next Erase arySheets On Error GoTo 0 ReDim arySheets(0 To clastcol - 2) For j = 2 To clastcol arySheets(j - 2) = thisSheet.Cells(i, j).Value Next j Worksheets(arySheets).Copy ActiveWorkbook.SendMail thisSheet.Cells(i, 1).Value ActiveWorkbook.Close savechanges:=False Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Steve" wrote in message oups.com... I'm sure I saw this done a year or so ago, when I was looking something else up, but naturally can't find it now. I'm trying to set up an automated distribution list, triggered by a command button. I know it can be done with the code: ActiveWorkbook.SendMail ", ") etc ... but I want different sheets in the workbook to go to different people. Therefore I'd set up a list with: A B C D dave@... sheet1 sheet4 sheet 5 bob@... sheet1 sheet2 sheet3 derek@... sheet4 sheet5 sheet6 Is there a way to have a macro look down column A and then send the listed sheets to the appropriate address? I'm assuming that there'd be a VLookup equivalent needed..? Thanks in advance Steve |
#3
|
|||
|
|||
Maybe you like to use a template to do this
http://www.rondebruin.nl/mail/templates.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Bob Phillips" wrote in message ... Steve, Here is some code to get you started. The SendMail didn't work for me, but as I never use SendMail, I have no idea why. Perhaps you will. It just picks up the address from column A Sub SendSheets() Dim thisSheet As Worksheet Dim cLastRow As Long Dim clastcol As Long Dim arySheets Dim i As Long, j As Long cLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row Set thisSheet = ActiveSheet For i = 1 To cLastRow clastcol = thisSheet.Cells(i, Columns.Count).End(xlToLeft).Column On Error Resume Next Erase arySheets On Error GoTo 0 ReDim arySheets(0 To clastcol - 2) For j = 2 To clastcol arySheets(j - 2) = thisSheet.Cells(i, j).Value Next j Worksheets(arySheets).Copy ActiveWorkbook.SendMail thisSheet.Cells(i, 1).Value ActiveWorkbook.Close savechanges:=False Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Steve" wrote in message oups.com... I'm sure I saw this done a year or so ago, when I was looking something else up, but naturally can't find it now. I'm trying to set up an automated distribution list, triggered by a command button. I know it can be done with the code: ActiveWorkbook.SendMail ", ") etc ... but I want different sheets in the workbook to go to different people. Therefore I'd set up a list with: A B C D dave@... sheet1 sheet4 sheet 5 bob@... sheet1 sheet2 sheet3 derek@... sheet4 sheet5 sheet6 Is there a way to have a macro look down column A and then send the listed sheets to the appropriate address? I'm assuming that there'd be a VLookup equivalent needed..? Thanks in advance Steve |
#4
|
|||
|
|||
The SendMail didn't work for me, but as I never use SendMail, I have
no idea why. Perhaps you will. Bahahahahaha! Yee-esss. I find it hard enough wording my questions in ways you guys would understand. Knowing how and why VB works is way over my head. :) Thanks for the reply Bob, it works perfectly. I'll probably go with Ron's template, though. Steve |
#5
|
|||
|
|||
This may get posted twice as there was a server error.
Anyhow, thanks a lot for the template Ron; it does exactly what I needed. Steve |
#6
|
|||
|
|||
I knew Ron had some code for SendMail, but I didn't realise he catered for
using it in this type of situation. Perhaps it's time to read that page again. Bob "Steve" wrote in message oups.com... The SendMail didn't work for me, but as I never use SendMail, I have no idea why. Perhaps you will. Bahahahahaha! Yee-esss. I find it hard enough wording my questions in ways you guys would understand. Knowing how and why VB works is way over my head. :) Thanks for the reply Bob, it works perfectly. I'll probably go with Ron's template, though. Steve |
#7
|
|||
|
|||
You are welcome
-- Regards Ron de Bruin http://www.rondebruin.nl "Steve" wrote in message oups.com... This may get posted twice as there was a server error. Anyhow, thanks a lot for the template Ron; it does exactly what I needed. Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using VB, specific cell data into email subject | Excel Discussion (Misc queries) | |||
background color showing only when in email form | Excel Discussion (Misc queries) | |||
Multiple sheets selected | Excel Discussion (Misc queries) | |||
Hyperlink to specific sheet in Excel Web File | Links and Linking in Excel | |||
How do I include cell data in subject line of email address/hyperl | Excel Worksheet Functions |