I have 2 issues. First, I need to identify the text after " - " in worksheet1 A1 (this cell is always Title - "local site" with "local site" changing based on where the user is). The formula I thought was working (Right(rng, InStr(rng, " ") - 6).Value) grabbed the last 3 digits (my site is ABC) on the right but the length of the site will vary.
Once I have that, I need to use it as part of the subject for an email I want to write using a macro: site " summary " date (for example, ABC Summary April 26)
Dim subj As String
Dim site As String
Dim rng As Range
Sheets("worksheet1").Select
Set rng = Range("A1")
site = Right(rng, InStr(rng, " ") - 6).Value ' this formula won't work for all sites. For now, it returns "ABC"
DateDM = Format(Date, "MMMM DD")
subj = site & " Summary - " & DateDM
When I run the code to start my email, my Subject starts with Summary and nothing I've tried adds on the site.
Once I have that, I need to use it as part of the subject for an email I want to write using a macro: site " summary " date (for example, ABC Summary April 26)
Dim subj As String
Dim site As String
Dim rng As Range
Sheets("worksheet1").Select
Set rng = Range("A1")
site = Right(rng, InStr(rng, " ") - 6).Value ' this formula won't work for all sites. For now, it returns "ABC"
DateDM = Format(Date, "MMMM DD")
subj = site & " Summary - " & DateDM
When I run the code to start my email, my Subject starts with Summary and nothing I've tried adds on the site.