I have a vbs scropt that I schedule using taskmanager in windows7.
the vbs script opens an excel workbook and runs a certain macro.
This all works pretty well for most cases except this example where I want to copy a picture in the workbook to an email. the vba works fine when i open the workbook and run the vba but when i run the vbs(double click on it) I get an error.
This is the line that throws the issue Plage.CopyPicture with CopyPicture method of Range class failed
I have done a bit of searching on this and came here and here. From this the best solution I could fine is by doing this in my vbs script myExcelWorker.Visible = True
Whilst this works, which is good, I would like to know if there is a another way without making it visible? Does anyone on here have any ideas?
NOTE: I don't fully understand why it works when it is made visible. Is there anything I can do with the clipboard.
--------------------------------------------------------------------EDIT1-------------------------------------------------
I tried adding Plage.CopyPicture 2 as per comment below and got the same error
Run-time error ‘-2147417848 (80010108)’ Method ‘CopyPicture’ of Object ‘Range’ failed I then press debug and VB editor comes up with the error, I press f8 again and i get this error Run-time error ‘1004’: CopyPicture method of Range class failed
Below are my scripts just for reference:
VBS script:
'need to update WBName & MacroName here as this is fairly generic
dim WshShell
set WshShell = CreateObject("Wscript.Shell")
dim strPath
strPath = WshShell.CurrentDirectory
Dim myExcelWorker
Set myExcelWorker = CreateObject("Excel.Application")
'myExcelWorker.Visible = True ' this makes excel visible
dim oWorkBook
dim WBName
WBName = "\WBwithMacro.xlsm" 'WB to be opened
dim MacroName
MacroName = "'" & strpath & WBName & "'!UpdateChart_EDW_LTE" 'Macro Name to be run
'Write Start+strPath to log file
Call WriteLog("Start_XXX",strPath,"var3")
'Write Mid+strPath+WBName to log file
Call WriteLog("Mid___XXX",strpath & WBName,"var3")
'open WB for running macro
'set oWorkBook = myExcelWorker.Workbooks.open(strpath & WBName) 'for WB WITHOUT password
Set oWorkBook = myExcelWorker.Workbooks.Open(strpath & WBName,,,,"","Password") 'for WB with password
'Write MacroName to log file
Call WriteLog("Mid___XXX",MacroName,"var3")
myExcelWorker.Run MacroName
myExcelWorker.DisplayAlerts = False 'this is required so the WB will save without being prompted
oWorkBook.Save
oWorkBook.Close
myExcelWorker.DisplayAlerts = True ' set it back to true again as it is good practice
myExcelWorker.Quit
'Write End to log file
Call WriteLog("End___XXX","t2","t3")
set oWorkBook = Nothing
set myExcelWorker = Nothing
set WshShell = Nothing
'sub to write to log file
Sub WriteLog(var1, var2, var3)
Dim objShell
Set objShell = WScript.CreateObject("WScript.Shell")
'Wscript.Echo "VBSStart.vbs is running"
Dim ObjFso
Dim StrFileName
Dim ObjFile
Dim FlName
'WScript.Echo var1 & ",,,," & var2
FlName = "TestFile.txt"
StrFileName = objShell.CurrentDirectory & "\" & FlName
Set ObjFso = CreateObject("Scripting.FileSystemObject")
'Creating a file for writing data
set ObjFile = ObjFso.OpenTextFile(StrFileName, 8, True)
'Writing a string into the file
ObjFile.WriteLine(var1 & "," & var2 & "," & var3 & "," & now)
'Closing the file
ObjFile.Close
' Using Set is mandatory
Set objShell = Nothing
End Sub
VBA part(in excel workbook):
Function createPng(Namesheet, nameRange, nameFile)
Debug.Print "Namesheet: " & Namesheet
Debug.Print "nameRange: " & nameRange
Debug.Print "nameFile: " & nameFile
ThisWorkbook.Activate
Worksheets(Namesheet).Activate
Set Plage = ThisWorkbook.Worksheets(Namesheet).Range(nameRange)
Plage.CopyPicture
With ThisWorkbook.Worksheets(Namesheet).ChartObjects.Add(Plage.Left, Plage.Top, Plage.Width, Plage.Height)
.Activate
.Chart.Paste
.Chart.Export Environ$("temp") & "\" & nameFile & ".png", "png"
End With
Debug.Print Environ$("temp") & "\" & nameFile & ".png", "png"
Worksheets(Namesheet).ChartObjects(Worksheets(Namesheet).ChartObjects.Count).Delete
Set Plage = Nothing
End Function
Sub sendMail()
Application.Calculation = xlManual
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Dim TempFilePath As String
Dim wsName, rngForImg, fnForImg As String ' e.g. "Sheet1", "B2:I27", "BasicSendEmail"
wsName = "DM"
rngForImg = "A1:N32"
fnForImg = "DM" 'this will be basically the name of the Img
Debug.Print "wsName: " & wsName ' the ws name
Debug.Print "rngForImg: " & rngForImg ' the range you want in the Img
Debug.Print "fnForImg: " & fnForImg ' the name you want for the Img
'Create a new Microsoft Outlook session
Set appOutlook = CreateObject("outlook.application")
'create a new message
Set Message = appOutlook.CreateItem(olMailItem)
With Message
.Subject = "PNG My mail auto Object PNG" & Now
.HTMLBody = "<span LANG=EN>" _
& "<p class=style2><span LANG=EN><font FACE=Calibri SIZE=3>" _
& "Hello,<br ><br >The weekly dashboard is available " _
& "<br>Find below an overview :<BR>"
'first we create the image as a png file
Call createPng(wsName, rngForImg, fnForImg)
'we attached the embedded image with a Position at 0 (makes the attachment hidden)
TempFilePath = Environ$("temp") & "\"
Debug.Print "TempFilePath: " & TempFilePath
.Attachments.Add TempFilePath & fnForImg & ".png", olByValue, 0
'Then we add an html <img src=''> link to this image
'Note than you can customize width and height - not mandatory
.HTMLBody = .HTMLBody & "<br><B>WEEKLY REPPORT:</B><br>" _
& "<img src='cid:" & fnForImg & ".png '" & "><br>" _
& "<br>Best Regards,<br>Ed</font></span>"
.To = "a@a.com; a@a.com;"
.Cc = "a@a.com;"
.Display
.Send
End With
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Application.Calculation = xlCalculationAutomatic
End Sub
