DragonWood
02-06-2018, 01:01 PM
Greetings,
I have created a workbook that, among other things, will send an email to other engineers in the region to request equipment needed for an upcoming job. Most of it works, except the Array.
I have a page in the workbook called "Lists". On this page is a column that contains the names, email addresses, and phone numbers of the other engineers in the region. However, not all regions have 20 engineers, so sometimes the list will not be complete.
The problem is two-fold:
First, how do you account for the blank cells in the array?
Second, how do I actually copy the data into the RegionalEmail variable?
Here is my code so far.
Sub EquipmentRequestEmail()
'Sends an email to all regional Field Engineers to request equipment.
Dim CellsToCheck As Variant
Dim cll As Variant
Dim RegionalEmail As String
Dim strSubject As String
Dim strBody As String
Dim objApp As Object
Dim objMail As Object
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
With ActiveWorkbook.Worksheets("Lists")
CellsToCheck = Array("J22", "J26", "J30", "J34", "J38", "J42", "J46", "J50", "J54", "J58", "J62", "J66", "J70", "J74", "J78", "J82", "J86", "J90", "J94")
For Each cll In CellsToCheck
With .Range(cll)
.Copy
End With
Next cll
End With
RegionalEmail = CellsToCheck
strSubject = "Equipment Request"
Set objApp = CreateObject("Outlook.Application")
Set objMail = objApp.CreateItem(0)
On Error Resume Next
With objMail
.To = RegionalEmail
.Subject = strSubject
.Body = "Guys," & vbNewLine & vbNewLine & vbTab & "I need a (Insert Equipment Here) for next week please." & vbNewLine & vbNewLine
.Display
End With
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set objMail = Nothing
Set objApp = Nothing
End Sub
Thanks in advance.
I have created a workbook that, among other things, will send an email to other engineers in the region to request equipment needed for an upcoming job. Most of it works, except the Array.
I have a page in the workbook called "Lists". On this page is a column that contains the names, email addresses, and phone numbers of the other engineers in the region. However, not all regions have 20 engineers, so sometimes the list will not be complete.
The problem is two-fold:
First, how do you account for the blank cells in the array?
Second, how do I actually copy the data into the RegionalEmail variable?
Here is my code so far.
Sub EquipmentRequestEmail()
'Sends an email to all regional Field Engineers to request equipment.
Dim CellsToCheck As Variant
Dim cll As Variant
Dim RegionalEmail As String
Dim strSubject As String
Dim strBody As String
Dim objApp As Object
Dim objMail As Object
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
With ActiveWorkbook.Worksheets("Lists")
CellsToCheck = Array("J22", "J26", "J30", "J34", "J38", "J42", "J46", "J50", "J54", "J58", "J62", "J66", "J70", "J74", "J78", "J82", "J86", "J90", "J94")
For Each cll In CellsToCheck
With .Range(cll)
.Copy
End With
Next cll
End With
RegionalEmail = CellsToCheck
strSubject = "Equipment Request"
Set objApp = CreateObject("Outlook.Application")
Set objMail = objApp.CreateItem(0)
On Error Resume Next
With objMail
.To = RegionalEmail
.Subject = strSubject
.Body = "Guys," & vbNewLine & vbNewLine & vbTab & "I need a (Insert Equipment Here) for next week please." & vbNewLine & vbNewLine
.Display
End With
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set objMail = Nothing
Set objApp = Nothing
End Sub
Thanks in advance.