Sub Macro1()
'
' Macro1 Macro
' email.
'
' Keyboard Shortcut: Ctrl+q
'
Dim objOutlook As New Outlook.Application, ccstring As String ' The Dim (Dimension) statement tells the Editor what to expect when you assign something to a "Variable".
Dim objMessage As MailItem, spacestring As Integer ' A variable can hold a program or object name like outlook mail message
Dim subject As String, body As String, EmailAddress As String, tempemailaddress As String ' or a string which is text
Dim lastrow As Long, count As Integer, Wksh As Worksheet, count2 As Long, found As Integer ' or an integer, long or double which hold mathematical values
On Error GoTo errorcatch ' if an error occurs in the code GoTo the line called errorcatch and display the error type and stop the code
body = "Please complete your task for the CMT number mentioned above." & Chr(13) ' chr(13) is the Asci code for a carriage Return
body = body & "This task is now past-due and at risk of cancelation. " & Chr(13)
body = body & "Please let me know if there is something I can do to assist to get this task completed by end of day. "
Set Wksh = Sheets("SAPBW_DOWNLOAD") ' the word Set tells the Editor you are setting an Object to the variable
Wksh.Select ' select the worksheet now called Wksh
lastrow = Wksh.Cells(Rows.count, "A").End(xlUp).Row ' find the last row by coming up the worksheet until it finds a filled cell in Column A
Application.ScreenUpdating = False ' turn off the screen updating to make the code run faster and less distracting to the user
For count = 125 To lastrow ' This the start of a Loop using For and Next, there are other types like Do/Until and While/Wend, count is the counter being used for this loop which will look at the rows of the worksheet
If Cells(count, 14) > 4 Then ' using the Cells(row, column) reference system test if the cell value is greater than 4
EmailAddress = "" ' if the value is >4 then set the emailaddress to nothing in readines to add items to it
tempemailaddress = Cells(count, 12) ' set the temp email address to a Cell contents
If Left(tempemailaddress, 1) = " " Then tempemailaddress = Right(Cells(count, 12), Len(Cells(count, 12)) - 1) ' test for a space at the start of the email address and remove it
spacestring = InStr(1, tempemailaddress, " ") ' using the instring functio test if there are spaces in the email address indicating more than one email address
If spacestring = 0 Then ' if there is NO space assign the contents to the EmailAddress on the next line
EmailAddress = tempemailaddress
Else ' or if there isn't one do the following
found = 1 ' set the space found indicator to 1
For count2 = 2 To Len(tempemailaddress) ' start a second loop to go through the characters in the cell looking for spaces
If Mid(tempemailaddress, count2, 1) = " " Then ' test for space
EmailAddress = EmailAddress & ";" & Mid(tempemailaddress, found, count2 - found) ' a space is found so add the part before space to the EmailAddress with a ";"
found = count2 ' reset the space found indicator to where the space was found
'MsgBox EmailAddress & " - " & found & " = " & count2 ' this is not currently used but provides the user with a message box to show the current EmailAddress
End If ' the end of the if/the/else test
Next count2 ' the incremtor of count2
EmailAddress = Right(EmailAddress, Len(EmailAddress) - 1) ' remove any spare ; from the end of the EmailAddress
End If ' the end of the test for spaces
subject = Cells(count, 5) ' set the email subject to a cell content
ccstring = Cells(count, 2) ' set the email CC to a cell content
'MsgBox EmailAddress
'GoTo skip
Set objMessage = objOutlook.CreateItem(olMailItem) ' start Outlook and set up a new email
With objMessage ' the word with means use code associated with previous Set command
.To = EmailAddress
.CC = ccstring
.subject = subject
.body = body
.Display
'.Send
End With
Set objOutlook = Nothing 'reset Outlook
Set objMessage = Nothing ' and the email
End If ' the end of the first if question looking at Cell(count, 14)
skip:
Next count ' increment the first counter which will move testing down to the next row until it reaches "Lastrow".
Application.ScreenUpdating = True
MsgBox "emails have been sent." ' tell the user that the emails should have been sent assuming nothing goes wrong with outlook etc
Exit Sub ' stop the code and return to the worksheet
errorcatch: ' the line where you want the GoTo to go to to declare the error
MsgBox Err.Description
End Sub
