Hi, I’ve never done anything with macros before and I’m having some problems with the following code I found for emailing individual worksheets (for training schedules in my case) from within a excel workbook: http://msdn.microsoft.com/en-us/library/bb268022.aspx#Excel2007DifferentWaysEmail_SendingaSingleWorksheetbyEMail
I’d really appreciate some help with someone who understands this because I keep going around in circles.
I tried to change what the file name will save as with the “B1” and “B2” values within the current worksheet. This will give a more specific course date and time than the original code which used the name of the workbook and the current date which is not useful in my case.
The problem is this works with the first file I save but every other workbook with different cell values saves as the same file name as the first one I did?? How do I reset this and make it recognise the new cell values??
'Save the new workbook and then mail it.
TempFilePath = Environ$("temp") & "\"
FN = Range("B1")
FN2 = Range("B2")
TempFileName = FN & " - " & FN2
With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
On Error Resume Next
.SendMail "(e-mail address removed)", _
"Copy of Training Program."
On Error GoTo 0
.Close SaveChanges:=False
End With
' Delete the file you just sent.
Kill TempFilePath & TempFileName & FileExtStr
The other thing I’m hoping to achieve with this is that instead of it always saying “Copy of Training Program” in the subject of the emails I would like it to use the same “B1” and “B2” values with the course name and date. Can this be done?
Like I said any help would be greatly appreciated!
I’d really appreciate some help with someone who understands this because I keep going around in circles.
I tried to change what the file name will save as with the “B1” and “B2” values within the current worksheet. This will give a more specific course date and time than the original code which used the name of the workbook and the current date which is not useful in my case.
The problem is this works with the first file I save but every other workbook with different cell values saves as the same file name as the first one I did?? How do I reset this and make it recognise the new cell values??
'Save the new workbook and then mail it.
TempFilePath = Environ$("temp") & "\"
FN = Range("B1")
FN2 = Range("B2")
TempFileName = FN & " - " & FN2
With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
On Error Resume Next
.SendMail "(e-mail address removed)", _
"Copy of Training Program."
On Error GoTo 0
.Close SaveChanges:=False
End With
' Delete the file you just sent.
Kill TempFilePath & TempFileName & FileExtStr
The other thing I’m hoping to achieve with this is that instead of it always saying “Copy of Training Program” in the subject of the emails I would like it to use the same “B1” and “B2” values with the course name and date. Can this be done?
Like I said any help would be greatly appreciated!