Export cell range, save as jpg in file and no duplicates

[Lisea123]

I have this vba code that I have attached to a button to save the cell range as a jpg to a file on my computer. I've attached the macor to a button and this code works great. However, when I click the button again, it saves the new screenshot over the older one because it is saving it as the same name. What I need is to save the screenshot as a jpg but with different names each time the button is clicked. (i.e. screenshot1.jpg, screenshot2.jpg, etc...) Please have a look at my code and let me know if there is a line I can add to it for this new feature to work.

[code:]
Sub ExportScreenshot()
Const FName As String = "n:\lise\scrabble\Screenshot.jpg"
Dim pic_rng As Range
Dim ShTemp As Worksheet
Dim ChTemp As Chart
Dim PicTemp As Picture
Application.ScreenUpdating = False
Set pic_rng = Worksheets("7-Tiles").Range("B4:n9")
Set ShTemp = Worksheets.Add
Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:=ShTemp.Name
Set ChTemp = ActiveChart
pic_rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture
ChTemp.Paste
Set PicTemp = Selection
With ChTemp.Parent
.Width = PicTemp.Width + 8
.Height = PicTemp.Height + 8
End With
ChTemp.Export Filename:="n:\lise\scrabble\Screenshot.jpg", FilterName:="jpg"
Application.DisplayAlerts = False
ShTemp.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
[/code:]

 

[sergioMabres]

Hi Lisea
You could try using this line
ChTemp.Export Filename:="n:\lise\scrabble\Screenshot_"& Format(Now(), "yyyymmddhhmmss") &".jpg", FilterName:="jpg"


Instead of this line
ChTemp.Export Filename:="n:\lise\scrabble\Screenshot.jpg", FilterName:="jpg"

Cheers
Sergio

 

[Lisea123]

Hi Lisea
You could try using this line
ChTemp.Export Filename:="n:\lise\scrabble\Screenshot_"& Format(Now(), "yyyymmddhhmmss") &".jpg", FilterName:="jpg"


Instead of this line
ChTemp.Export Filename:="n:\lise\scrabble\Screenshot.jpg", FilterName:="jpg"

Cheers
Sergio

THANK YOU!!!!!That works perfectly.

 

[sergioMabres]

Glad to help!

 

[Lisea123]

Glad to help!

Here's a different question. if I change the "filename" to "mailto:" can I send this screenshot via email instead of having it save on a drive?

 

[sergioMabres]

I think not, sending mails is always a hot topic in MrExcel forum, because is full of different requirements, every one has its particular best practice and minuscules detailed list of wishes, you can even use VBA in Outlook to send all files in a directory, but that you will have to look in a Outlook forum.
Cheers
Sergi

 

[Lisea123]

I think not, sending mails is always a hot topic in MrExcel forum, because is full of different requirements, every one has its particular best practice and minuscules detailed list of wishes, you can even use VBA in Outlook to send all files in a directory, but that you will have to look in a Outlook forum.
Cheers
Sergi

Thank you for your quick responses!