Hi All
I am trying to get a worksheets to create and email attachment if Cell AM4 = "Mon". AM4 has the formula to return the date from another work sheet and is formatted to only display week days as "ddd".
the code I have does not display any errors, but also does not function as intended:
I am trying to get a worksheets to create and email attachment if Cell AM4 = "Mon". AM4 has the formula to return the date from another work sheet and is formatted to only display week days as "ddd".
the code I have does not display any errors, but also does not function as intended:
VBA Code:
Sub Email_Shifts_Test()
'
'Test Shift Weekly email and figures
'
'
Dim month_folder As String
Dim Path As String
Dim Name As String
Dim Name2 As String
Sheets("Scrap by Shift").Select
month_folder = Format(Range("AO4").Value, "00") & " " & MonthName(Range("AO4").Value)
Path = "\\FS002\Dept$\Quality\07 Reports\03 Daily Quality Report\00 Report PDF Archive\Shift Data\" & month_folder
Name = Path & "\" & "Shift Data " & Format(Range("AN4"), "yyyy.mm.dd ddd") & ".pdf"
Name2 = Path & "\" & "Shift Data Week " & Range("G1") & ".pdf"
Sheets("Scrap by Shift").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Name, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
[B] Sheets("Shift Wkly Scrap").Select
'
'
'Add weekly sheet on Mondays
'
If Range("AM4").Value = "Mon" Then
Pdf_ShiftsWkly
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Name2, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
End If[/B]
'Send as email
Set dam = CreateObject("Outlook.Application").CreateItem(0)
dam.To = "dnorm <dnorm@email.com>" 'Put your emails here to CC
dam.Subject = "TEST - Daily Shift Data Report for " & Range("AN4")
dam.Body = "Good Morning" & vbCr & vbCr & _
"Please find enclosed the Daily Shift Data Report for " & Format(Range("AN4"), "dddd dd/mmmm/yyyy") & vbCr & vbCr & _
"Regards" & vbCr & vbCr & "Quality"
dam.Attachments.Add Name
'
'
'Attach weekly sheet if Monday
'
[B] If Range("AM4").Value = "Mon" Then
dam.Attachments.Add Name2
End If[/B]
dam.Display
'Switch to .Send to send the mail .Display to view email
End Sub