Sendkeys Timing

  • Thread starter Thread starter John Strung
  • Start date Start date
J

John Strung

I have a script that, when you press a button in Accees, starts Outlook
(if it is not running), opens a new message, and puts data from various
fields in the Access database into the To:, Subject: and body of the
e-mail message and then inserts the Outlook sig file.

Much of this is done by SendKeys (yes, I know the shortcomings of
Sendkey, but haven't been able to figure out how else to do this). This
works fine with our older computers, but our new work stations seem to
be too fast for the script and things get a bit scrambled.

Is there any VBA instructions tht I can put between the lines of script
to insert, say, a .1 second pause between each set of SendKeys
instructions near the end of the script?

In the alternative, can anyone suggest how to eliminate the necessity to
use SendKeys. The script is below:

-----------

Private Sub email_button_Click()

Dim varx As String, lawinits As String

Dim usnm As String, criteria As String, MyDB As Database

Dim MySet As DAO.Recordset, emaccess As String, nomatch As Variant

Dim Msg As String, x As Long

On Error GoTo emailerrorhandler

DoCmd.GoToControl "FindBox"

If IsNull([E-Mail]) Then Exit Sub 'EXIT IF NO E-MAIL ADDRESS

usnm = Environ("username")

Set MyDB = CurrentDb()

Set MySet = MyDB.OpenRecordset("xref")

criteria = "usnames = '" & usnm & "'"

MySet.FindFirst criteria 'finds the first instance of the
username in the xref table

If nomatch Then

Msg = "Problem - speak to John Strung"

Exit Sub

End If

lawinits = MySet![inits]

emaccess = MySet![internetaccess]

DoCmd.Close A_TABLE, "XREF"



If emaccess = "no" Then Exit Sub


x = AppActivateClass("rctrl_renwnd32")

If x = 0 Then


varx = InputBox("Enter MS Mail Password", "MS Mail Password")

If Len(CStr(varx)) < 3 Then Exit Sub


x = Shell("C:\Program Files\Microsoft Office\Office10\OUTLOOK.EXE", 1)

SendKeys varx & "{enter}", True


End If



SendKeys "^{f10}", True 'zoom window"

Dim olApp As Object

Dim objNewMail As Object

Dim objONewmail As Object

Dim AddressString As String, SubjectString As String

Dim bodystring1 As String, bodystring2 As String, bodystring3 As String

Set olApp = New Outlook.Application

Set objONewmail = olApp.CreateItem(olMailItem)

Set objNewMail = CreateObject("REDEMPTION.SafeMailItem")

objNewMail.Item = objONewmail

With objNewMail

AddressString = CStr(Me!)

SubjectString = CStr([File Name])

bodystring1 = "Our File Number: " & CStr([File Number]) & Chr(10)

bodystring2 = ""

bodystring3 = ""

If IsNull(Me![Claimno]) = 0 Then bodystring2 = "Your Claim Number: "
& CStr(Me![Claimno]) & Chr(10)

If IsNull(Me![DateLoss]) = 0 Then bodystring3 = "Date of Loss: " &
CStr(Me![DateLoss]) & Chr(10)



bodystring1 = bodystring1 & bodystring2 & bodystring3 &
"-------------------------" & Chr(10) & Chr(10)

..Recipients.Add AddressString

..Subject = SubjectString

..Body = bodystring1

..Display



SendKeys "{tab}", True

SendKeys "{pgdn}", True

SendKeys "%i", False

SendKeys "s", False

SendKeys "{enter}", True

SendKeys "{pgup}", True

If IsNull(Me![Claimno]) = 0 Then SendKeys "{down}", True

If IsNull(Me![DateLoss]) = 0 Then SendKeys "{down}", True

SendKeys "{down}", True

SendKeys "{down}", True

End With


Exit Sub



emailerrorhandler:

On Error GoTo 0

MsgBox "Open the In Box in MS Outlook and try again"

Exit Sub

End Sub
 
I'm not sure what you're doing with SendKeys so I can't comment on
alternatives. However, I've almost never found it necessary to use
SendKeys except to call a custom help file in a COM addin, where a bug
makes it necessary. If you explain more about what you are doing maybe
someone can suggest alternatives.

See if sprinkling in DoEvents statements in your code after each
SendKeys helps.




John Strung said:
I have a script that, when you press a button in Accees, starts Outlook
(if it is not running), opens a new message, and puts data from various
fields in the Access database into the To:, Subject: and body of the
e-mail message and then inserts the Outlook sig file.

Much of this is done by SendKeys (yes, I know the shortcomings of
Sendkey, but haven't been able to figure out how else to do this). This
works fine with our older computers, but our new work stations seem to
be too fast for the script and things get a bit scrambled.

Is there any VBA instructions tht I can put between the lines of script
to insert, say, a .1 second pause between each set of SendKeys
instructions near the end of the script?

In the alternative, can anyone suggest how to eliminate the necessity to
use SendKeys. The script is below:

-----------

Private Sub email_button_Click()

Dim varx As String, lawinits As String

Dim usnm As String, criteria As String, MyDB As Database

Dim MySet As DAO.Recordset, emaccess As String, nomatch As Variant

Dim Msg As String, x As Long

On Error GoTo emailerrorhandler

DoCmd.GoToControl "FindBox"

If IsNull([E-Mail]) Then Exit Sub 'EXIT IF NO E-MAIL ADDRESS

usnm = Environ("username")

Set MyDB = CurrentDb()

Set MySet = MyDB.OpenRecordset("xref")

criteria = "usnames = '" & usnm & "'"

MySet.FindFirst criteria 'finds the first instance of the
username in the xref table

If nomatch Then

Msg = "Problem - speak to John Strung"

Exit Sub

End If

lawinits = MySet![inits]

emaccess = MySet![internetaccess]

DoCmd.Close A_TABLE, "XREF"



If emaccess = "no" Then Exit Sub


x = AppActivateClass("rctrl_renwnd32")

If x = 0 Then


varx = InputBox("Enter MS Mail Password", "MS Mail Password")

If Len(CStr(varx)) < 3 Then Exit Sub


x = Shell("C:\Program Files\Microsoft
Click to expand...
Office\Office10\OUTLOOK.EXE", 1)
SendKeys varx & "{enter}", True


End If



SendKeys "^{f10}", True 'zoom window"

Dim olApp As Object

Dim objNewMail As Object

Dim objONewmail As Object

Dim AddressString As String, SubjectString As String

Dim bodystring1 As String, bodystring2 As String, bodystring3 As String

Set olApp = New Outlook.Application

Set objONewmail = olApp.CreateItem(olMailItem)

Set objNewMail = CreateObject("REDEMPTION.SafeMailItem")

objNewMail.Item = objONewmail

With objNewMail

AddressString = CStr(Me!)

SubjectString = CStr([File Name])

bodystring1 = "Our File Number: " & CStr([File Number]) & Chr(10)

bodystring2 = ""

bodystring3 = ""

If IsNull(Me![Claimno]) = 0 Then bodystring2 = "Your Claim Number: "
& CStr(Me![Claimno]) & Chr(10)

If IsNull(Me![DateLoss]) = 0 Then bodystring3 = "Date of Loss: " &
CStr(Me![DateLoss]) & Chr(10)



bodystring1 = bodystring1 & bodystring2 & bodystring3 &
"-------------------------" & Chr(10) & Chr(10)

.Recipients.Add AddressString

.Subject = SubjectString

.Body = bodystring1

.Display



SendKeys "{tab}", True

SendKeys "{pgdn}", True

SendKeys "%i", False

SendKeys "s", False

SendKeys "{enter}", True

SendKeys "{pgup}", True

If IsNull(Me![Claimno]) = 0 Then SendKeys "{down}", True

If IsNull(Me![DateLoss]) = 0 Then SendKeys "{down}", True

SendKeys "{down}", True

SendKeys "{down}", True

End With


Exit Sub



emailerrorhandler:

On Error GoTo 0

MsgBox "Open the In Box in MS Outlook and try again"

Exit Sub

End Sub[/QUOTE]
Click to expand...
 
Thanks, Ken. The part of the script I am concerned with is this part:


-------------

bodystring1 = "Our File Number: " & CStr([File Number]) & Chr(10)

bodystring2 = ""

bodystring3 = ""

If IsNull(Me![Claimno]) = 0 Then bodystring2 = "Your Claim Number: "
& CStr(Me![Claimno]) & Chr(10)

If IsNull(Me![DateLoss]) = 0 Then bodystring3 = "Date of Loss: " &
CStr(Me![DateLoss]) & Chr(10)



bodystring1 = bodystring1 & bodystring2 & bodystring3 &
"-------------------------" & Chr(10) & Chr(10)

..Recipients.Add AddressString

..Subject = SubjectString

..Body = bodystring1

..Display



SendKeys "{tab}", True

SendKeys "{pgdn}", True

SendKeys "%i", False

SendKeys "s", False

SendKeys "{enter}", True

SendKeys "{pgup}", True

If IsNull(Me![Claimno]) = 0 Then SendKeys "{down}", True

If IsNull(Me![DateLoss]) = 0 Then SendKeys "{down}", True

SendKeys "{down}", True

SendKeys "{down}", True

End With

--------------------

Basically, what the part down to the line ".Display" does, is to insert
one to three lines of text from one to three fields of an access data
base into the body of an e-mail message. (Basically the three lines are
our file number, the client's claim number and a date of loss, all
followed by a line of hyphens and a couple of carriage returns -- the
effect is to make a heading or re line for the message that is more
comprehensive than the single line Subject line.)

I then want a gap between the heading and the signature line, and I
ultimately want the cursor to be in that gap when control is turned over
to the user to start typing. Then next section of the script tries to
accomplish that.

The send keys lines then add the Signature file and attept to place the
cursor between the line of hyphens and the signature, by moving back up
to the top of the message and counting down to where the cursor should
go.

This works fine on our older computers but in the newer ones, either the
SendKey fires off before the lines of text get inserted (so that the
signature is above the message heading, rather than below it) or the
wrong menu is activated and instead of the "s" inserting the signature
file, it sends the message.
 
Other than setting the Wait argument on all the SendKeys calls or
putting in DoEvents statements in between the calls to SendKeys I
don't have any suggestions for those calls.

Out of curiosity, why are you separating lines with only a linefeed
and not also a carriage return or using vbCRLF, which is usable in
VBS?




John Strung said:
Thanks, Ken. The part of the script I am concerned with is this part:


-------------

bodystring1 = "Our File Number: " & CStr([File Number]) & Chr(10)

bodystring2 = ""

bodystring3 = ""

If IsNull(Me![Claimno]) = 0 Then bodystring2 = "Your Claim Number: "
& CStr(Me![Claimno]) & Chr(10)

If IsNull(Me![DateLoss]) = 0 Then bodystring3 = "Date of Loss: " &
CStr(Me![DateLoss]) & Chr(10)



bodystring1 = bodystring1 & bodystring2 & bodystring3 &
"-------------------------" & Chr(10) & Chr(10)

.Recipients.Add AddressString

.Subject = SubjectString

.Body = bodystring1

.Display



SendKeys "{tab}", True

SendKeys "{pgdn}", True

SendKeys "%i", False

SendKeys "s", False

SendKeys "{enter}", True

SendKeys "{pgup}", True

If IsNull(Me![Claimno]) = 0 Then SendKeys "{down}", True

If IsNull(Me![DateLoss]) = 0 Then SendKeys "{down}", True

SendKeys "{down}", True

SendKeys "{down}", True

End With

--------------------

Basically, what the part down to the line ".Display" does, is to insert
one to three lines of text from one to three fields of an access data
base into the body of an e-mail message. (Basically the three lines are
our file number, the client's claim number and a date of loss, all
followed by a line of hyphens and a couple of carriage returns -- the
effect is to make a heading or re line for the message that is more
comprehensive than the single line Subject line.)

I then want a gap between the heading and the signature line, and I
ultimately want the cursor to be in that gap when control is turned over
to the user to start typing. Then next section of the script tries to
accomplish that.

The send keys lines then add the Signature file and attept to place the
cursor between the line of hyphens and the signature, by moving back up
to the top of the message and counting down to where the cursor should
go.

This works fine on our older computers but in the newer ones, either the
SendKey fires off before the lines of text get inserted (so that the
signature is above the message heading, rather than below it) or the
wrong menu is activated and instead of the "s" inserting the signature
file, it sends the message.
Click to expand...
 
Thanks, Ken. I don't remember why I was using a linefeed and not a carriage
return, but that much seems to work, so I will not tinker with it.

I tried the DoEvents and changing the False to a True in the sendkeys
statement, but it didn't seem to help. Is there an Outlook VBA equivalent of
the Access DoMenuItem command that I could use to access the Signature file
instead of SendKeys? I cannot seem to find any documentation for such a
command.

Ken Slovak - said:
Other than setting the Wait argument on all the SendKeys calls or
putting in DoEvents statements in between the calls to SendKeys I
don't have any suggestions for those calls.

Out of curiosity, why are you separating lines with only a linefeed
and not also a carriage return or using vbCRLF, which is usable in
VBS?




John Strung said:
Thanks, Ken. The part of the script I am concerned with is this part:


-------------

bodystring1 = "Our File Number: " & CStr([File Number]) & Chr(10)

bodystring2 = ""

bodystring3 = ""

If IsNull(Me![Claimno]) = 0 Then bodystring2 = "Your Claim Number: "
& CStr(Me![Claimno]) & Chr(10)

If IsNull(Me![DateLoss]) = 0 Then bodystring3 = "Date of Loss: " &
CStr(Me![DateLoss]) & Chr(10)



bodystring1 = bodystring1 & bodystring2 & bodystring3 &
"-------------------------" & Chr(10) & Chr(10)

.Recipients.Add AddressString

.Subject = SubjectString

.Body = bodystring1

.Display



SendKeys "{tab}", True

SendKeys "{pgdn}", True

SendKeys "%i", False

SendKeys "s", False

SendKeys "{enter}", True

SendKeys "{pgup}", True

If IsNull(Me![Claimno]) = 0 Then SendKeys "{down}", True

If IsNull(Me![DateLoss]) = 0 Then SendKeys "{down}", True

SendKeys "{down}", True

SendKeys "{down}", True

End With

--------------------

Basically, what the part down to the line ".Display" does, is to insert
one to three lines of text from one to three fields of an access data
base into the body of an e-mail message. (Basically the three lines are
our file number, the client's claim number and a date of loss, all
followed by a line of hyphens and a couple of carriage returns -- the
effect is to make a heading or re line for the message that is more
comprehensive than the single line Subject line.)

I then want a gap between the heading and the signature line, and I
ultimately want the cursor to be in that gap when control is turned over
to the user to start typing. Then next section of the script tries to
accomplish that.

The send keys lines then add the Signature file and attept to place the
cursor between the line of hyphens and the signature, by moving back up
to the top of the message and counting down to where the cursor should
go.

This works fine on our older computers but in the newer ones, either the
SendKey fires off before the lines of text get inserted (so that the
signature is above the message heading, rather than below it) or the
wrong menu is activated and instead of the "s" inserting the signature
file, it sends the message.
Click to expand...
Click to expand...
 
It can't be found because it doesn't exist :)

In Outlook code the equivalent is to get the CommandBar object that
represents a menu and then the CommandBarControl object that
represents the menu command and call its Execute method.

I have some sample code on my Web site that shows how to execute the
Send or Send/Receive menu commands. Something like that could be
modified to call a menu command for adding a signature. The sample
code is at http://www.slovaktech.com/code_samples.htm#SendReceive
 
Thanks, Ken. I have had a look at your code, and I think I could probably
modify it for my purposes, all though that would be a real stretch of my
Outlook programming abilities. I guess the major confusion I have is that
your code presumably finds the menu command in the main Outlook menubar
which is different from the new message menu bar which is the one which has
the insert signature command. Can you assist me on how to ensure that I
access the correct menubar?
 
ActiveExplorer is the currently active folder view. ActiveInspector is
the currently active open item. So if you substitute ActiveInspector
for ActiveExplorer you would get a handle to the open item.
 
Ken, I have tried to adapt your code to my purposes and the result is the
following which stops with an error "invalid procedure call or argument at
the line
"Set ObjCB = objOutlook.ActiveInspector.CommandBars("MenuBar")"

Thanks for sticking with me through this.


-----------
Dim olApp As Object
Dim objNewMail As Object
Dim objONewmail As Object
Dim SubjectString As String
Dim bodystring1 As String, bodystring2 As String, bodystring3 As String

'next 5 lines added per Ken Slovak's code
Dim objOutlook As Outlook.Application
Dim ObjCtl As Office.CommandBarControl
Dim ObjPop As Office.commandBarPopup
Dim ObjCB As Office.CommandBar
Dim ObjItem As Object

'Next section creates a new message and inserts text into various fields
Set olApp = New Outlook.Application
Set objONewmail = olApp.CreateItem(olMailItem)
Set objNewMail = CreateObject("REDEMPTION.SafeMailItem")
objNewMail.Item = objONewmail

With objNewMail

SubjectString = CStr(Forms![Full Screen]![File Name])
bodystring1 = "Our File Number: " & CStr(Forms![Full Screen]![File Number])
& Chr(10)

bodystring2 = ""

If IsNull(Forms![Full Screen]![DateLoss]) = 0 Then bodystring2 = "Date of
Loss: " & CStr(Forms![Full Screen]![DateLoss]) & Chr(10)

bodystring1 = bodystring1 & bodystring2 & "-------------------------" &
Chr(10) & Chr(10)
..Recipients.Add Address
..Subject = SubjectString
..Body = bodystring1
..Display
SendKeys "{tab}", True
SendKeys "{pgdn}", True

'comment out old sendkeys section to insert the sig file into body of
message
'SendKeys "%i", False 'the next three lines insert the Sig file
'SendKeys "s", True
'SendKeys "{enter}", True

'new section added per Ken Slovak's code to insert sig file in lieu of
sendkeys
Set objOutlook = CreateObject("Outlook.Application")
Set ObjCB = objOutlook.ActiveInspector.CommandBars("MenuBar")

Set ObjPop = ObjCB.Controls("Insert")
Set ObjCtl = ObjPop.Controls("Signature")
ObjCtl.Execute

Set ObjCtl = Nothing
Set ObjPop = Nothing

Set ObjCB = Nothing

Set ObjItem = Nothing
Set objOutlook = Nothing
 
Is an item open at that time? ActiveInspector is invalid if no item is
actually opened in an Inspector.

I'd try breaking it down further to see where the error is creeping
in. For example set an Inspector object to .ActiveInspector. See if
you have a valid Inspector. Then in the Immediate window see what
CommandBars are there by using .ActiveInspector.CommandBars.Count and
..ActiveInspector.CommandBars(1).Name and so on.




msnews.microsoft.com said:
Ken, I have tried to adapt your code to my purposes and the result is the
following which stops with an error "invalid procedure call or argument at
the line
"Set ObjCB = objOutlook.ActiveInspector.CommandBars("MenuBar")"

Thanks for sticking with me through this.


-----------
Dim olApp As Object
Dim objNewMail As Object
Dim objONewmail As Object
Dim SubjectString As String
Dim bodystring1 As String, bodystring2 As String, bodystring3 As String

'next 5 lines added per Ken Slovak's code
Dim objOutlook As Outlook.Application
Dim ObjCtl As Office.CommandBarControl
Dim ObjPop As Office.commandBarPopup
Dim ObjCB As Office.CommandBar
Dim ObjItem As Object

'Next section creates a new message and inserts text into various fields
Set olApp = New Outlook.Application
Set objONewmail = olApp.CreateItem(olMailItem)
Set objNewMail = CreateObject("REDEMPTION.SafeMailItem")
objNewMail.Item = objONewmail

With objNewMail

SubjectString = CStr(Forms![Full Screen]![File Name])
bodystring1 = "Our File Number: " & CStr(Forms![Full Screen]![File Number])
& Chr(10)

bodystring2 = ""

If IsNull(Forms![Full Screen]![DateLoss]) = 0 Then bodystring2 = "Date of
Loss: " & CStr(Forms![Full Screen]![DateLoss]) & Chr(10)

bodystring1 = bodystring1 & bodystring2 & "-------------------------" &
Chr(10) & Chr(10)
.Recipients.Add Address
.Subject = SubjectString
.Body = bodystring1
.Display
SendKeys "{tab}", True
SendKeys "{pgdn}", True

'comment out old sendkeys section to insert the sig file into body of
message
'SendKeys "%i", False 'the next three lines insert the Sig file
'SendKeys "s", True
'SendKeys "{enter}", True

'new section added per Ken Slovak's code to insert sig file in lieu of
sendkeys
Set objOutlook = CreateObject("Outlook.Application")
Set ObjCB = objOutlook.ActiveInspector.CommandBars("MenuBar")

Set ObjPop = ObjCB.Controls("Insert")
Set ObjCtl = ObjPop.Controls("Signature")
ObjCtl.Execute

Set ObjCtl = Nothing
Set ObjPop = Nothing

Set ObjCB = Nothing

Set ObjItem = Nothing
Set objOutlook = Nothing
Click to expand...
 
Thanks, Ken. There is an object open. The earlier part of the script has
just opened a new message and filled in the To: and Subject: fields. I
will poke around as you suggest in the morning.
 
Ken, with your help, I am getting a lot closer. I can now use the modified
version of your VBA script to insert the signature without using sendkeys.
Revised code is below. However, for some reason, the Outlook does not
respond to the remaining send keys commands which are basically there to
position the cursor. Any ideas why not?

-----------------------------
Dim olApp As Object
Dim objNewMail As Object
Dim objONewmail As Object
Dim SubjectString As String
Dim bodystring1 As String, bodystring2 As String, bodystring3 As String

Dim objOutlook As Outlook.Application
Dim ObjCtl As Office.CommandBarControl
Dim ObjPop As Office.commandBarPopup
Dim ObjCB As Office.CommandBar
Dim ObjItem As Object

Set olApp = New Outlook.Application
Set objONewmail = olApp.CreateItem(olMailItem)
Set objNewMail = CreateObject("REDEMPTION.SafeMailItem")
objNewMail.Item = objONewmail



With objNewMail

SubjectString = CStr(Forms![Full Screen]![File Name])
bodystring1 = "Our File Number: " & CStr(Forms![Full Screen]![File Number])
& Chr(10)

bodystring2 = ""

If IsNull(Forms![Full Screen]![DateLoss]) = 0 Then bodystring2 = "Date of
Loss: " & CStr(Forms![Full Screen]![DateLoss]) & Chr(10)

bodystring1 = bodystring1 & bodystring2 & "-------------------------" &
Chr(10) & Chr(10)
..Recipients.Add Address
..Subject = SubjectString
..Body = bodystring1
..Display

'The next two lines are supposed to move the cursor to the bottom of the
message body
'prior to inserting the signature, but seem to be ignored by Outlook

SendKeys "{tab}", True
SendKeys "{pgdn}", True

'The next section inserts the signature file

Set objOutlook = CreateObject("Outlook.Application")
Set ObjCB = objOutlook.ActiveInspector.CommandBars("Menu Bar")
Set ObjPop = ObjCB.Controls("Insert")
Set ObjPop = ObjPop.Controls("Signature")
Set ObjCtl = ObjPop.Controls.Item(1)

ObjCtl.Execute
Set ObjCtl = Nothing
Set ObjPop = Nothing

Set ObjCB = Nothing

Set ObjItem = Nothing
Set objOutlook = Nothing


SendKeys "{pgup}", True 'the next three lines reposition the cursor just
above the sig file
If IsNull(Forms![Full Screen]![DateLoss]) = 0 Then SendKeys "{down}", True
SendKeys "{down}", True
SendKeys "{down}", True



End With
 
I have spent a little longer poking around with this by setting a breakpoint
and stepping through the script. For some reason the sendkeys instructions
are completely ignored. I have tried setting them to "false" instead of
true, but to no avail. Is there any way to use VBA to set the position of
the cursor before the insertion of the signature?
 
I'm not sure about using SendKeys to position the cursor. I never use
SendKeys in my code except when I'm calling a help file for
context-sensitive help in an addin property page to work around a bug
in HTML Help.

I suppose one question would be what has the focus when you use
SendKeys? If the keys are being sent to somewhere in the form that
doesn't understand the keys or the focus is some other place then
SendKeys would never work.

So, if you stopped the code before you use SendKeys where is the
focus? Do things work better if you do what you are doing in the
Outlook MailItem and not in the Redemption item? You might want to do
that and go to the Redemption item only to add recipients to the
message after doing the body.
 
I thought the focus was on the correct form, but perhaps I could use
SetFocus to set the focus to the Body field on the form? Is that possible? I
have been struggling with trying to do that for about an hour but can't seem
to get the syntax right.
 
No, unfortunately there's no way I know of to set focus on the Body
property control unless you are using the Word editor and then you
wouldn't need to go through these gyrations.
 
I had the same problem with Word, Excel and Outlook working together o
newer computers.

Not sure if this will help but I use the following code to format som
text in word before pasting it into Outlook, I don't have the Acces
Object library at work and therefore I don't know if Application.Ontim
is supported by Access.

The concept is that the Excel code copies some data to the clipboard
then opens the Word file which runs the code below:



Sub DailyImpactReport()

' Pause for 15 seconds.
Application.OnTime When:=Now + TimeValue("00:00:02"), _
Name:="MyMainMacro"

End Sub


Sub MyMainMacro()
Selection.Paste
Selection.MoveUp Unit:=wdLine, Count:=1
Selection.Tables(1).Select

Selection.Rows.ConvertToText Separator:=wdSeparateByTabs
Application.OnTime When:=Now + TimeValue("00:00:02"), _
Name:="MyMainMacro2"
End Sub
Sub MyMainMacro2()
Selection.WholeStory
Selection.Copy
Application.OnTime When:=Now + TimeValue("00:00:02"), _
Name:="MyMainMacro3"
End Sub
Sub MyMainMacro3()
Dim olapp As New Outlook.Application
Dim olMail As Outlook.MailItem

Set olMail = olapp.CreateItem(olMailItem)

olMail.Subject = "Daily Impact Report"
olMail.To = "Daily Impact Report"
olMail.Body = ""

olMail.Display
'Be kind to your environment and clean
'up your unused objects
Set olMail = Nothing
Set olapp = Nothing
Application.OnTime When:=Now + TimeValue("00:00:05"), _
Name:="MyMainMacro4"


End Sub
Sub MyMainMacro4()
VBA.Interaction.sendkeys ("^v")
ActiveDocument.Close (False)
Application.Quit
End Su
 
Back
Top