troubleshooting Question

Excel VBA- capture screenshot image dynamically

Avatar of "Abys" Wallace
"Abys" WallaceFlag for United States of America asked on
Visual Basic ClassicMicrosoft ExcelVB Script
7 Comments1 Solution4584 ViewsLast Modified:
Hi Experts!  :)

I posted a question a few days ago regarding taking a screenshot of an excel worksheet's data area and then embedding that screenshot into the body of an email message composed in MS Outlook 2010.  The expert who assisted successfully answered my question and the code works great!

Below is a link to the previous answered post with the sample code and workbook:
Previous Screenshot/ Embed Image in Outlook 2010 email message body post

My question now is it possible to make it dynamic meaning if the data area size changes is it possible to have to code auto adjust to the new data range?  Right now its set for the current size A1:P40 in this part of the code.
Private Sub cmdEmail_Click()


Dim oRange As Range
Dim oCht As Chart
Dim oImg As Picture

Set oRange = Range("A1:P40")
Set oCht = Charts.Add

oRange.CopyPicture xlScreen, xlPicture
oCht.Paste
oCht.Export Filename:="C:\Temp\SavedRange.jpg", Filtername:="JPG"

Screenshot_Mail "Sample Email Address" & "; " & "Sample Email Address", "Sample Email Address" & _
 "; " & "Sample Email Address" & "; " & "Sample Email Address", "Rep II Case Productivity Report", "<font color=red>" & _
"<I>" & "Below is a Snapshot View of the Rep II Case Productivity Report:  " & "</font>" & "</I>" & _
"<BR>" & "<BR>" & "<BODY><FONT face=Arial color=#000080 size=2></FONT>" & _
"<IMG alt='' hspace=0 src='C:\Temp\SavedRange.jpg' align=baseline border=0>&nbsp;</BODY>"

DoEvents
oCht.Delete



End Sub
Sample-Workbook.xlsm
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros