VBA copy paste from Excel to email while screen is locked

[outofbounds]

I have several scripts that run overnight using a task scheduler to create a PDF of an Excel report and email them out for me. These all work great .

I have another specific need to have a script copy/paste a selection from a spreadsheet directly into an email and send that. It works well when I run it manually. However, when the computer is locked and the script tries to run at night, it errors out because the clipboard in Windows will not allow copy/paste activities while the screen is locked.

Is there a way around this, or is there an alternative app to the native Windows clipboard that will allow me to copy/paste behind a locked screen

Thank you in advance for your ideas

 

[6StringJazzer]

It seems this should be possible by a direct assignment rather than a Copy/Paste. In fact that should be preferable. But can you show us the code you currently use? I suggest using code tags to preserve the formatting (select the code in your post and click the little VBA button).

 

[outofbounds]

It seems this should be possible by a direct assignment rather than a Copy/Paste. In fact that should be preferable. But can you show us the code you currently use? I suggest using code tags to preserve the formatting (select the code in your post and click the little VBA button).

here is my vba code: if there is a different way than using the copy and pasteandformat, i would be very grateful!

Sub EmailReportFile()

Dim strText As String
Dim outlook As Object
Dim newEmail As Object
Dim XInspect As Object
Dim pageEditor As Object
Dim wsDistro As Worksheet:  Set wsDistro - sheets("Distribution")
Dim wsTablePage As Worksheet:  Set wsTablePage = Sheets ("report data")

Set outlook - CreateObject("Outlook.application")
Set newEmail = outlook.CreateItem(0)

With newEmail
 .To - wsDistro.Range("B2").Value
 .CC = ""
 .BCC = ""
 .Subject = wsDistro.Range("B3").Value
 .display

  Set xInspect = newEmail.GetInspector
  Set pageEditor - xInspect.WordEditor

  wsTablePage.Range("B3.K53").Copy

  pageEditor.Application.Selection.Start = Len(.body)
  pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
  pageEditor.Application.Selection.PasteAndFormat  (wdFormatPlainText)
  .display
  .send
  Set pageEditor = Nothing
  Set xInspect = Nothing
End With

Set newEmail = Nothing
Set outlook = Nothing

End Sub

 

[6StringJazzer]

Did you copy/paste this code directly from the VBA editor? There are threefour lines that don't compile because they have "-" instead of "=". This makes me wonder if there are other errors.

 

[6StringJazzer]

Found another one

wsTablePage.Range("B3.K53").Copy

should be

wsTablePage.Range("B3:K53").Copy

Please get your code cleaned up before I take this any further.

 

[outofbounds]

Did you copy/paste this code directly from the VBA editor? There are threefour lines that don't compile because they have "-" instead of "=". This makes me wonder if there are other errors.

I didn't copy paste it, so those are just my transcription errors when I re-typed the code to get it into this message thread. My work computer is not allowed to connect to any Internet message boards, so please forgive those minor typos when I transcribed. The code on my vba compiles and operates fine. It only does not work when the screen is locked due to constraints that clipboard has with locked screens.
Thanks!

 

[6StringJazzer]

I haven't forgotten about this but haven't found a solution yet, either.