VBA email sheet based on week day

[dnorm]

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:

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

 

[diddi]

how does the code not work? what sort of error are we looking for

 

[dnorm]

Hi diddi

I think the error which was making the sub go to End If was the formula in AM4, having change it from a simple cell format to a "=TEXT(AP4,"ddd")" format has clear the lack of error noted.
I think the VBA was reading the formula as a number range not a text range, even though it was displayed as text.

Sorry, I know the indents are all over the place in my VBA's. I did make the VBA area I wanted looking at in bold, but when the message displayed, the bold had gone.

 

[dnorm]

how does the code not work? what sort of error are we looking for

Thank you for you post, I will take note on the use of indents to highlight areas I want looking at in future. Now to try and set a dedicated printer and print on Mondays lol.

 

[Peter_SSs]

I did make the VBA area I wanted looking at in bold, but when the message displayed, the bold had gone

If you want to bold (or any other format) vba code in the forum, use the RICH tags, not the VBA tags.

 

[dnorm]

If you want to bold (or any other format) vba code in the forum, use the RICH tags, not the VBA tags.

View attachment 31539

Thank you and noted!