VBA Help Copy/Attach/Email

agentkramr

Board Regular
Joined
Dec 27, 2021
Messages
98
Platform
  1. Windows
So my VBA takes a screen shot of a selection of cells, attaches the workbook in XLSX format , and then emails it.

VBA Code:
Sub Test1_Hourly()

Dim oApp As Object, oMail As Object, FileStr As String

Dim NewWb As Workbook, cnt As Integer

Dim FileName As String, MailSub As String, MailTxt As String

Dim tmpImageName As String

'************************************************* ********

'Set email details; Comment out if not required

Const MailTo = "my email"

'Const MailCC = "[EMAIL]some2@someone.com[/EMAIL]"

'Const MailBCC = "[EMAIL]some3@someone.com[/EMAIL]"

MailSub = "Test Hourly Counts"

MailTxt = ""



ThisWorkbook.Save



Workbooks("Test Hourly Counts Auto").RefreshAll



'Turns off screen updating

Application.ScreenUpdating = False

Sheets("1 Hour Counts").Unprotect "Test"

'define a temp path for your image

tmpImageName = Environ$("temp") & "" & "TempChart.jpg"

'create image file

Call CreateJpg("1 Hour Counts", Sheets("1 Hour Counts").Range("A1:T50"))

'copy range to new wb/remove formulas

Set NewWb = Workbooks.Add

'copy all sheets

For cnt = 1 To ThisWorkbook.Sheets.Count

ThisWorkbook.Sheets(cnt).Copy NewWb.Sheets(cnt)

Next cnt

NewWb.Sheets("1 Hour Counts").Range("A1:T50").Copy

NewWb.Worksheets("1 Hour Counts").Range("A1").PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False

'NewWb.Worksheets("1 Hour Counts").Shapes("Rectangle: Rounded Corners 1").Delete

'NewWb.Worksheets("1 Hour Counts").Shapes("Rectangle: Rounded Corners 2").Delete

NewWb.Worksheets("1 Hour Counts").Activate

NewWb.Worksheets("1 Hour Counts").Range("A1").Select

Application.DisplayAlerts = False

NewWb.Worksheets("Sheet1").Delete

NewWb.SaveAs FileName:=Left(ThisWorkbook.FullName, Len(ThisWorkbook.FullName) - 5) & ".xlsx", _

FileFormat:=xlOpenXMLWorkbook

Application.DisplayAlerts = True

FileStr = NewWb.FullName

NewWb.Close

Sheets("1 Hour Counts").Protect "Test"



'Creates and shows the outlook mail item



Set oApp = CreateObject("Outlook.Application")



Set oMail = oApp.CreateItem(0)



With oMail



.To = MailTo



.Cc = MailCC



.Bcc = MailBCC



.Subject = MailSub



.HTMLBody = "<body><img src=" & "'" & tmpImageName & "'/></body>"



.Attachments.Add FileStr



.Display



.Send



End With



'Deletes the temporary file



'WB.ChangeFileAccess Mode:=xlReadOnly



Kill (Environ$("temp") & "" & "TempChart.jpg")



Kill FileStr





'Restores screen updating and release Outlook



Application.ScreenUpdating = True



Set oMail = Nothing



Set oApp = Nothing



'Save Workbook



ThisWorkbook.Save



End Sub



Public Sub CreateJpg(SheetName As String, xRgAddrss As Range)

'creates temp JPG file of range (xRgAddrss) by creating temp chart

'uses current wb sheet (sheetname) to locate temp chart

Dim xRgPic As Range

Worksheets(SheetName).Activate

Set xRgPic = xRgAddrss

xRgPic.CopyPicture Appearance:=xlScreen, Format:=xlPicture

With ThisWorkbook.Worksheets(SheetName).ChartObjects.Add(xRgPic.Left, xRgPic.Top, _

xRgPic.Width, xRgPic.Height)

.Activate

.Chart.Paste

.Chart.Export Environ$("temp") & "" & "TempChart.jpg", "JPG"

End With

Worksheets(SheetName).ChartObjects(Worksheets(SheetName).ChartObjects.Count).Delete

OpenClipboard (0&)

EmptyClipboard

CloseClipboard



End Sub

I have this same VBA in 5 other workbooks i use a VBS
Code:
Dim objExcel
Dim objWorkBook
Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.Open("C:\Users\automatedreports\Desktop\Automated Reports\Hourly\Test 1")
On Error Resume Next
objExcel.DisplayAlerts=False
objExcel.Run("Test1_Hourly")
objExcel.DisplayAlerts=True
objWorkBook.Close True
Set objWorkBook = objExcel.Workbooks.Open("C:\Users\automatedreports\Desktop\Automated Reports\Hourly\Test 2")
On Error Resume Next
objExcel.DisplayAlerts=False
objExcel.Run("Test2_Hourly")
objExcel.DisplayAlerts=True
objWorkBook.Close False
objExcel.Quit
Set objWorkBook = Nothing
Set objExcel = Nothing
to kick the automated task of running them one after another. No matter which one i put as the beginning one it fails at
VBA Code:
xRgPic.CopyPicture Appearance:=xlScreen, Format:=xlPicture
with

VBA Error 1004 - CopyPicture method of Range class failed​


it will only do this for whichever one runs first, so if i swap them it is the same results. if i run each one seperately from excel they work fine, if i run them seperately from a vbs then run fine, if i run them together with the vbs it works fine, the problem ONLY happens during the the windows scheduled task that i have created that starts the vbs for me

i have chased this issue for about a week now. i have tried putting in wait timers in the vba and the vbs, i have tried the vba in different ways it ALWAYS comes down to this error
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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
Back
Top