| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending to email different email address based on cell value
Hi
I have the code below working for me, to copy a sheet to a blank workbook and email just this sheet. However I need to add something to it, so if a certain cell value = 00M send to one particular email address and if the cell value = 00C send to a different email address. Many thanks Sub Email_Memo() Range("B2:M34").Select Selection.Copy Workbooks.Add ActiveSheet.Paste Range("A1").Select ActiveWindow.DisplayGridlines = False ActiveWindow.Zoom = 75 With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveWindow .DisplayHeadings = False .DisplayHorizontalScrollBar = False .DisplayVerticalScrollBar = False .DisplayWorkbookTabs = False End With With ActiveSheet.PageSetup .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 .PrintErrors = xlPrintErrorsDisplayed End With ActiveWorkbook.SendMail "), ("Memo From Sto ") & Sheets("Sheet1").Range("H4").Value MsgBox "Your details have been sent", vbInformation, "PAYROLL MEMOS" ActiveWindow.Close SaveChanges = True Range("A1").Select End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sending to email different email address based on cell value
IF worksheets("???").Range("???")="OOM" THEN
ActiveWorkbook.SendMail "), ("Memo From Sto ") & Sheets("Sheet1").Range("H4").Value ELSEIF worksheets("???").Range("???")="OOC" THEN ActiveWorkbook.SendMail "), ("Memo From Sto ") & Sheets("Sheet1").Range("H4").Value END IF "Duncan" wrote: Hi I have the code below working for me, to copy a sheet to a blank workbook and email just this sheet. However I need to add something to it, so if a certain cell value = 00M send to one particular email address and if the cell value = 00C send to a different email address. Many thanks Sub Email_Memo() Range("B2:M34").Select Selection.Copy Workbooks.Add ActiveSheet.Paste Range("A1").Select ActiveWindow.DisplayGridlines = False ActiveWindow.Zoom = 75 With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveWindow .DisplayHeadings = False .DisplayHorizontalScrollBar = False .DisplayVerticalScrollBar = False .DisplayWorkbookTabs = False End With With ActiveSheet.PageSetup .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 .PrintErrors = xlPrintErrorsDisplayed End With ActiveWorkbook.SendMail "), ("Memo From Sto ") & Sheets("Sheet1").Range("H4").Value MsgBox "Your details have been sent", vbInformation, "PAYROLL MEMOS" ActiveWindow.Close SaveChanges = True Range("A1").Select End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sending to email different email address based on cell value
That works great. Thanks for that.
"Patrick Molloy" wrote: IF worksheets("???").Range("???")="OOM" THEN ActiveWorkbook.SendMail "), ("Memo From Sto ") & Sheets("Sheet1").Range("H4").Value ELSEIF worksheets("???").Range("???")="OOC" THEN ActiveWorkbook.SendMail "), ("Memo From Sto ") & Sheets("Sheet1").Range("H4").Value END IF "Duncan" wrote: Hi I have the code below working for me, to copy a sheet to a blank workbook and email just this sheet. However I need to add something to it, so if a certain cell value = 00M send to one particular email address and if the cell value = 00C send to a different email address. Many thanks Sub Email_Memo() Range("B2:M34").Select Selection.Copy Workbooks.Add ActiveSheet.Paste Range("A1").Select ActiveWindow.DisplayGridlines = False ActiveWindow.Zoom = 75 With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveWindow .DisplayHeadings = False .DisplayHorizontalScrollBar = False .DisplayVerticalScrollBar = False .DisplayWorkbookTabs = False End With With ActiveSheet.PageSetup .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 .PrintErrors = xlPrintErrorsDisplayed End With ActiveWorkbook.SendMail "), ("Memo From Sto ") & Sheets("Sheet1").Range("H4").Value MsgBox "Your details have been sent", vbInformation, "PAYROLL MEMOS" ActiveWindow.Close SaveChanges = True Range("A1").Select End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sending to email different email address based on cell value
thats Ok - a bit rough ... but see arjen's reply --- nice and tidy when you
have the time :) "Duncan" wrote: That works great. Thanks for that. "Patrick Molloy" wrote: IF worksheets("???").Range("???")="OOM" THEN ActiveWorkbook.SendMail "), ("Memo From Sto ") & Sheets("Sheet1").Range("H4").Value ELSEIF worksheets("???").Range("???")="OOC" THEN ActiveWorkbook.SendMail "), ("Memo From Sto ") & Sheets("Sheet1").Range("H4").Value END IF "Duncan" wrote: Hi I have the code below working for me, to copy a sheet to a blank workbook and email just this sheet. However I need to add something to it, so if a certain cell value = 00M send to one particular email address and if the cell value = 00C send to a different email address. Many thanks Sub Email_Memo() Range("B2:M34").Select Selection.Copy Workbooks.Add ActiveSheet.Paste Range("A1").Select ActiveWindow.DisplayGridlines = False ActiveWindow.Zoom = 75 With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveWindow .DisplayHeadings = False .DisplayHorizontalScrollBar = False .DisplayVerticalScrollBar = False .DisplayWorkbookTabs = False End With With ActiveSheet.PageSetup .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 .PrintErrors = xlPrintErrorsDisplayed End With ActiveWorkbook.SendMail "), ("Memo From Sto ") & Sheets("Sheet1").Range("H4").Value MsgBox "Your details have been sent", vbInformation, "PAYROLL MEMOS" ActiveWindow.Close SaveChanges = True Range("A1").Select End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sending to email different email address based on cell value
If Range("E14") = "" Then
MsgBox "A Pay Group Must Be Entered" At the start of the code, I have entered the above to check that the Pay Group is entered, and if not display a message box. How do i then stop the code from continuing as it is doing now, if there is no pay grup entered Cheers Duncan "Patrick Molloy" wrote: thats Ok - a bit rough ... but see arjen's reply --- nice and tidy when you have the time :) "Duncan" wrote: That works great. Thanks for that. "Patrick Molloy" wrote: IF worksheets("???").Range("???")="OOM" THEN ActiveWorkbook.SendMail "), ("Memo From Sto ") & Sheets("Sheet1").Range("H4").Value ELSEIF worksheets("???").Range("???")="OOC" THEN ActiveWorkbook.SendMail "), ("Memo From Sto ") & Sheets("Sheet1").Range("H4").Value END IF "Duncan" wrote: Hi I have the code below working for me, to copy a sheet to a blank workbook and email just this sheet. However I need to add something to it, so if a certain cell value = 00M send to one particular email address and if the cell value = 00C send to a different email address. Many thanks Sub Email_Memo() Range("B2:M34").Select Selection.Copy Workbooks.Add ActiveSheet.Paste Range("A1").Select ActiveWindow.DisplayGridlines = False ActiveWindow.Zoom = 75 With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveWindow .DisplayHeadings = False .DisplayHorizontalScrollBar = False .DisplayVerticalScrollBar = False .DisplayWorkbookTabs = False End With With ActiveSheet.PageSetup .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 .PrintErrors = xlPrintErrorsDisplayed End With ActiveWorkbook.SendMail "), ("Memo From Sto ") & Sheets("Sheet1").Range("H4").Value MsgBox "Your details have been sent", vbInformation, "PAYROLL MEMOS" ActiveWindow.Close SaveChanges = True Range("A1").Select End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro for Email - Sending to email addresses in cell | Excel Programming | |||
| Sending Email to non-standard address using CDO? | Excel Programming | |||
| sending email based on cell condition | Excel Discussion (Misc queries) | |||
| Excel spreadsheet sending email based on cell formula | Excel Worksheet Functions | |||
| Sending email via VB - address length | Excel Programming | |||
Linear Mode
