Excel 2016 Coding
Sub Send_Mail()
'
' Send_Mail Macro
'
'
Dim OutlookApp As Object
Dim OutlookMail As Object
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
'Let us create the email message and display it
'Make sure to change the parameters below
With OutlookMail
.To = "Ted.Regan@mwra.com;Dan.Keough@mwra.com;"
.Cc =
"Brendan.bowler@mwra.com;Robert.Farrell@mwra.com;Robert.Spencer@mwra.com;Scott.Shaw@mw
ra.com;Dana.Marshall@mwra.com;Dean.Pevey@mwra.com;Mark.Cataldo@mwra.com;John.Bates@m
wra.com"
.Bcc = "
.Subject = "Daily Dispatch"
.Body = "Core - Daily Dispatch " & Date
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
Set OutlookMail = Nothing
Set OutlookApp = Nothing
End Sub
Sub SendWorksheetByEmail()
Dim OutApp As Object
Dim OutMail As Object
Dim TempFilePath As String
Dim TempFileName As String
Dim FileFullPath As String
' Create a temporary file for the worksheet
TempFilePath = Environ$("temp") & "\"
TempFileName = "TempWorksheet" & Format(Now, "dd-mm-yy_hh-mm-ss") & ".xlsx"
FileFullPath = TempFilePath & TempFileName
ThisWorkbook.SaveCopyAs FileFullPath
' Create a new Outlook instance
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
' Compose the email
With OutMail
.Subject = "Worksheet"
.Body = "Please find the worksheet attached."
.Attachments.Add FileFullPath
.Display ' Use .Send instead of .Display to send without showing the email
End With
' Clean up
Set OutMail = Nothing
Set OutApp = Nothing
Kill FileFullPath ' Delete the temporary file
End Sub
To apply conditional formatting in Excel based on the date in another cell, you can use a formula-based
rule. Here's an example of how you can do it:
1. Select the cell or range of cells that you want to apply conditional formatting to.
2. Go to the "Home" tab in the Excel ribbon.
3. Click on the "Conditional Formatting" button in the "Styles" group.
4. From the dropdown menu, select "New Rule."
5. In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format."
6. In the "Format values where this formula is true" field, enter the formula that references the cell
containing the date. For example, if you want to format cells in column B based on the date in column A,
you can use a formula like:
`=$A1=TODAY()`
Note: The dollar sign ($) before the column letter locks the column reference when the formatting is
applied to other cells.
If you want to format cells based on a specific date instead of the current date, you can replace
`TODAY()` with the desired date in the formula.
7. Click on the "Format" button to specify the formatting you want to apply to the cells that meet the
condition.
8. Choose the desired formatting options, such as font color, fill color, etc.
9. Click "OK" to apply the conditional formatting rule.
10. The formatting will be applied to the selected cells based on the date in the referenced cell.
Remember to adjust the cell references and formula based on your specific requirements.
Sub ReturnAndFormatDate()
Dim dateValue As Date
' Set the date value
dateValue = DateSerial(2023, 5, 12) ' Change the year, month, and day as needed
' Assign the value to a cell
Range("A1").Value = dateValue
' Format the cell as a date
Range("A1").NumberFormat = "dd/mm/yyyy" ' Change the format as needed
End Sub
