Hi All,
New at VBA and requesting some assistance.
I have the following task to complete.
<tbody>
</tbody>
<tbody>
</tbody>The code is working well, however I also want the following:
Thanks in advance for your assistance.
New at VBA and requesting some assistance.
I have the following task to complete.
<tbody> </tbody> | ||||||||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | |||||||
0 | 1 | 0 |
<tbody> </tbody> | 12:00am | 1:00pm | |||||
0 | 1 | 0 |
<tbody> </tbody> | |||||||
0 | 1 | 0 |
<tbody> </tbody> | 5:00pm |
<tbody>
</tbody>
I have a separate sheet called employee emails with the names and emails of the employees. I want to send each employee their starting times for each day of the week and I am using the code below: Sub MonTimes() 'Working in 2000-2016 'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm Dim OutApp As Object Dim OutMail As Object Dim rng As Range Dim Ash As Worksheet Dim Cws As Worksheet Dim Rcount As Long Dim Rnum As Long Dim FilterRange As Range Dim FieldNum As Integer Dim mailAddress As String Dim NewWB As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim StrBody As String On Error GoTo cleanup Set OutApp = CreateObject("Outlook.Application") With Application .EnableEvents = False .ScreenUpdating = False End With 'Set filter sheet, you can also use Sheets("MySheet") Set Ash = ActiveSheet 'Set filter range and filter column (column with names) Set FilterRange = Ash.Range("D4:L" & Ash.Rows.Count) FieldNum = 1 'Filter column = A because the filter range start in column A 'Add a worksheet for the unique list and copy the unique list in A1 Set Cws = Worksheets.Add FilterRange.Columns(FieldNum).AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=Cws.Range("A1"), _ CriteriaRange:="", Unique:=True 'Count of the unique values + the header cell Rcount = Application.WorksheetFunction.CountA(Cws.Columns(1)) 'If there are unique values start the loop If Rcount >= 2 Then For Rnum = 2 To Rcount 'Look for the mail address in the EMPLOYEE EMAILS worksheet mailAddress = "" On Error Resume Next mailAddress = Application.WorksheetFunction. _ VLookup(Cws.Cells(Rnum, 1).Value, _ Worksheets("EMPLOYEE EMAILS").Range("A1:B" & _ Worksheets("EMPLOYEE EMAILS").Rows.Count), 2, False) On Error GoTo 0 If mailAddress <> "" Then 'Filter the FilterRange on the FieldNum column FilterRange.AutoFilter Field:=FieldNum, _ Criteria1:=Cws.Cells(Rnum, 1).Value 'Copy the visible data in a new workbook With Ash.AutoFilter.Range On Error Resume Next Set rng = .SpecialCells(xlCellTypeVisible) On Error GoTo 0 End With Set NewWB = Workbooks.Add(xlWBATWorksheet) rng.Copy With NewWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues .Cells(1).PasteSpecial Paste:=xlPasteFormats .Cells(1).Select Application.CutCopyMode = False End With 'Create a file name TempFilePath = Environ$("temp") & "" TempFileName = "TIME" & Ash.Parent.Name _ & " " & Format(Now, "dd-mmm-yy h-mm-ss") If Val(Application.Version) < 12 Then 'You use Excel 97-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007-2016 FileExtStr = ".xlsx": FileFormatNum = 51 End If 'Save, Mail, Close and Delete the file Set OutMail = OutApp.CreateItem(0) With NewWB .SaveAs TempFilePath & TempFileName _ & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next StrBody = "Hi," & " " & _ "Please check your TIME" & " " & _ "Thanks" & " " & _ With OutMail .To = mailAddress .Subject = "MONDAY TIMES" .Attachments.Add NewWB.FullName .HTMLBody = StrBody .Display 'Or use Send End With On Error GoTo 0 .Close savechanges:=False End With Set OutMail = Nothing Kill TempFilePath & TempFileName & FileExtStr End If 'Close AutoFilter Ash.AutoFilterMode = False Next Rnum End If cleanup: Set OutApp = Nothing Application.DisplayAlerts = False Cws.Delete Application.DisplayAlerts = True With Application .EnableEvents = True .ScreenUpdating = True End With End Sub |
<tbody>
</tbody>
- The cells in the range that do not have a value to be hidden (For each employee).
- If there are no values (no time) then the employee will not receive an email
- The code is currently working for the Header cells without formulas. How do we make it work if the cell contains a formula?
Thanks in advance for your assistance.