ExcelBanter - Sending to email different email address based on cell value

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Sending to email different email address based on cell value (https://www.excelbanter.com/excel-programming/432831-sending-email-different-email-address-based-cell-value.html)

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

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

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

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

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