VBA email sheet based on week day

dnorm

Board Regular
Joined
Dec 28, 2017
Messages
96
Office Version
  1. 365
Platform
  1. Windows
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:

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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,267
Office Version
  1. 2010
Platform
  1. Windows
how does the code not work? what sort of error are we looking for
 

dnorm

Board Regular
Joined
Dec 28, 2017
Messages
96
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

dnorm

Board Regular
Joined
Dec 28, 2017
Messages
96
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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.

1612785294112.png
 

Watch MrExcel Video

Forum statistics

Threads
1,127,348
Messages
5,624,145
Members
416,014
Latest member
MickP69

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top