Option Explicit
'Macro to send out report to each company individually
Sub Send_Reschedule_Report()
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 SigString As String
Dim Signature As String
Dim wbMailList As Workbook
Dim oWS As Worksheet
Dim wbThis As Workbook
On Error GoTo cleanup
Set OutApp = CreateObject("Outlook.Application")
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
' create a reference, wbThis, to the workbook the code is in
Set wbThis = ThisWorkbook
' loop through all the sheets in the workbook the code is in (wbThis)
For Each oWS In wbThis.Sheets
oWS.AutoFilterMode = False
oWS.UsedRange.Rows.Hidden = False
oWS.UsedRange.Columns.Hidden = False
Next
' Get_mail Macro
'
'
' open Vendor email list.xls and create a reference to it - wbMailList
Set wbMailList = Workbooks.Open(Filename:= _
"C:\Documents and Settings\xxxxxx\Desktop\Vendor email list.xlsx")
' copy the 'MailInfo' worksheet from workbook just opened to the workbook the code is in
wbMailList.Sheets("Mailinfo").Copy Before:=wbThis.Sheets(2)
' close the workbook opened earlier
wbMailList.Close
'Hide columns
wbThis.Sheets("Sheet1").Range("I:I,K:P").EntireColumn.Hidden = True
'Set filter sheet, you can also use Sheets("MySheet")
Set Ash = wbThis.Sheets("Sheet1")
'Set filter range and filter column (column with names)
Set FilterRange = Ash.Range("A1:Q" & 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 = wbThis.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 MailInfo worksheet
mailAddress = ""
On Error Resume Next
mailAddress = Application.WorksheetFunction. _
VLookup(Cws.Cells(Rnum, 1).Value, _
Worksheets("Mailinfo").Range("A1:B" & _
Worksheets("Mailinfo").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 = "reschedule " & Ash.Parent.Name _
& " " & Format(Now, "mm-dd-yy")
If Val(Application.Version) < 12 Then
'You use Excel 2000-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007-2010
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
SigString = "C:\Documents and Settings\xxxxx\Application Data\Microsoft\Signatures\Jason.htm"
If Dir(SigString) <> "" Then
Signature = Get_Signature(SigString)
Else
Signature = ""
End If
With OutMail
.To = mailAddress
.CC = "no one@xyzcorp.com"
.Subject = "Reschedules for " & Format(Now, "mm-dd-yy")
.Attachments.Add NewWB.FullName
.HTMLBody = "Hi, " & _
"Please review attached and get back to me with answers within the next 2 days." & _
"Thank you, " & Signature
.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
Function Get_Signature(ByVal sFile As String) As String
Dim fso As Object
Dim ts As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
Get_Signature = ts.ReadAll
ts.Close
End Function
