Hi
I read Bob Philips response in regards to disabling the security message
when emailing but I wanted to know how we could use the code attached to the
site when not all users get the security message.

I tried the message and on a computer that doesn't get the security message
the workbook would not email.

Any idea's???

Thanks
Noemi

Hi DawnTreader

See
http://www.rondebruin.nl/mail/prevent.htm

CDO is a good option

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message ...
Hello Noemi

how did you get the security message to go away?

i assume you mean the one where outlook warns that another program is
attempting to send email.

and someone please answer her question, i could use this as well! :)

thanks for any and all help.

"Noemi" wrote:

Hi
I read Bob Philips response in regards to disabling the security message
when emailing but I wanted to know how we could use the code attached to the
site when not all users get the security message.

I tried the message and on a computer that doesn't get the security message
the workbook would not email.

Any idea's???

Thanks
Noemi

See the note on my site

( SendKeys is not always reliable and this will not work on every computer)
Also you not know if Outlook is installed ?

CDO is a good option for you


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message ...
Hello again

here is where i am at:

Option Explicit
Sub SaveWorkbook()

Dim USRNM As String
Dim SunDT As String
Dim strFName As String
Dim wsh As Object
Dim fs As Object
Dim DocPath As String
Dim DirString As String
Dim OutApp As Object
Dim OutMail As Object

Set wsh = CreateObject("WScript.Shell")
Set fs = CreateObject("Scripting.FileSystemObject")
DocPath = wsh.SpecialFolders.Item("mydocuments")
DirString = DocPath & "\Time Cards"

With ActiveSheet
USRNM = .Range("EmployeeName").Value
SunDT = Format(.Range("SundayDate").Value, "dd-mmm-yy")

If Trim(USRNM) = "" Or Trim(SunDT) = "" Then
MsgBox "Please add Employee Name and Sunday's Date" & vbLf &
"File not saved!"
Else
If Not fs.FolderExists(DirString) Then
fs.CreateFolder DirString
End If
strFName = "TimeCard " & SunDT & " " & USRNM & ".xls"
.Parent.SaveAs DirString & "\" & strFName
End If

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = strFName
.Body = "Here is the time card for the period starting " & SunDT
.Attachments.Add ActiveWorkbook.FullName
' You can add other files also like this
' .Attachments.Add ("C:\test.txt")
' .Display
' Application.Wait (Now + TimeValue("0:00:02"))
' Application.SendKeys "%S"
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

' ActiveWorkbook.SendMail ", strFName

' Application.Quit
End With

End Sub

i dont want the user to see anything. i want them to press a button and it
is gone. but this code with .send has the security warning. and if i use the
3 lines for .display i end up with the email waiting for the send button to
be pushed. i am thinking that either sendkeys doesnt do what i thought it did
or there is something not working right within those three lines.

help? and thanks in advance.

"Ron de Bruin" wrote:

Good morning

Not working with the SendMail examples
Only for the outlook object model examples that use (Send or Display)

See
http://www.rondebruin.nl/sendmail.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message ...
Hello

slight problem...

ActiveWorkbook.SendMail ", strFName
.Display
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%S"

thats the code i am trying to get to work, but it chokes on the .Display

what does that code do? i understand the rest, but i dont know what this
does or why? it is a 403 property or something not supported.

also do i replace the send mail line or do i need to move it?

"Ron de Bruin" wrote:

Hi DawnTreader

See
http://www.rondebruin.nl/mail/prevent.htm

CDO is a good option

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello Noemi

how did you get the security message to go away?

i assume you mean the one where outlook warns that another program is
attempting to send email.

and someone please answer her question, i could use this as well! :)

thanks for any and all help.

"Noemi" wrote:

Hi
I read Bob Philips response in regards to disabling the security message
when emailing but I wanted to know how we could use the code attached to the
site when not all users get the security message.

I tried the message and on a computer that doesn't get the security message
the workbook would not email.

Any idea's???

Thanks
Noemi

Try redemption or Click Yes


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message ...
Hello Ron

there are problems with CDO.

i dont know if everyone who uses this will have a outlook express account. i
cant spend the time going through each machine to make sure. and we cant
afford to start buggering up the current settings on each computer. just not
worth the time.

additionally i dont know if we have an in house SMTP server.

and finally i want this to end up using people's outlook folders so that
there is a record of it both on the senders end and the recievers end. i know
that the reciever would have a copy in the inbox, but with the CDO method it
seems that the sender would end up with no record of it being sent. this is
not conducive to "paper" trails.

thanks for you help. i will keep working it. :)
"Ron de Bruin" wrote:

See the note on my site

( SendKeys is not always reliable and this will not work on every computer)
Also you not know if Outlook is installed ?

CDO is a good option for you


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message ...
Hello again

here is where i am at:

Option Explicit
Sub SaveWorkbook()

Dim USRNM As String
Dim SunDT As String
Dim strFName As String
Dim wsh As Object
Dim fs As Object
Dim DocPath As String
Dim DirString As String
Dim OutApp As Object
Dim OutMail As Object

Set wsh = CreateObject("WScript.Shell")
Set fs = CreateObject("Scripting.FileSystemObject")
DocPath = wsh.SpecialFolders.Item("mydocuments")
DirString = DocPath & "\Time Cards"

With ActiveSheet
USRNM = .Range("EmployeeName").Value
SunDT = Format(.Range("SundayDate").Value, "dd-mmm-yy")

If Trim(USRNM) = "" Or Trim(SunDT) = "" Then
MsgBox "Please add Employee Name and Sunday's Date" & vbLf &
"File not saved!"
Else
If Not fs.FolderExists(DirString) Then
fs.CreateFolder DirString
End If
strFName = "TimeCard " & SunDT & " " & USRNM & ".xls"
.Parent.SaveAs DirString & "\" & strFName
End If

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = strFName
.Body = "Here is the time card for the period starting " & SunDT
.Attachments.Add ActiveWorkbook.FullName
' You can add other files also like this
' .Attachments.Add ("C:\test.txt")
' .Display
' Application.Wait (Now + TimeValue("0:00:02"))
' Application.SendKeys "%S"
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

' ActiveWorkbook.SendMail ", strFName

' Application.Quit
End With

End Sub

i dont want the user to see anything. i want them to press a button and it
is gone. but this code with .send has the security warning. and if i use the
3 lines for .display i end up with the email waiting for the send button to
be pushed. i am thinking that either sendkeys doesnt do what i thought it did
or there is something not working right within those three lines.

help? and thanks in advance.

"Ron de Bruin" wrote:

Good morning

Not working with the SendMail examples
Only for the outlook object model examples that use (Send or Display)

See
http://www.rondebruin.nl/sendmail.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello

slight problem...

ActiveWorkbook.SendMail ", strFName
.Display
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%S"

thats the code i am trying to get to work, but it chokes on the .Display

what does that code do? i understand the rest, but i dont know what this
does or why? it is a 403 property or something not supported.

also do i replace the send mail line or do i need to move it?

"Ron de Bruin" wrote:

Hi DawnTreader

See
http://www.rondebruin.nl/mail/prevent.htm

CDO is a good option

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello Noemi

how did you get the security message to go away?

i assume you mean the one where outlook warns that another program is
attempting to send email.

and someone please answer her question, i could use this as well! :)

thanks for any and all help.

"Noemi" wrote:

Hi
I read Bob Philips response in regards to disabling the security message
when emailing but I wanted to know how we could use the code attached to the
site when not all users get the security message.

I tried the message and on a computer that doesn't get the security message
the workbook would not email.

Any idea's???

Thanks
Noemi

With normal excel menu options you can use .execute
but not with the Send button in the Outlook mail

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message ...
Hello again

here is a thought...

is there a way in vba to specifically hit a button on a toolbar? something
like:

.toolbar(1).button(1).activate

something like that? if there is couldnt we specify the email toolbar and
send button to be activated? i know that is what the sendkeys is supposed to
do, but there is a problem with that method. i have been looking at the %S
and i have noticed that the % is the alt key and the S is intended to hit the
send button. but i dont think sendkeys takes into account that you have to
hold the alt key in order to make that idea work. create an email in outlook
and hold the alt key then while still holding alt and hit the S key. it
sends. alt and then S is nothing. there is no menu in the email creationg
window that has an underlined S.

the real problem here is that i have no menu that has a send option on it.
if there was it would be as simple as using send keys to hit the alt key,
then the menu letter that was underlined and then the underlined key for
send. that would work, and it would always work. currently M$ has screwed up
and made the send function only connected to a button that we cant hit.

any other thoughts on this?

"Ron de Bruin" wrote:

See the note on my site

( SendKeys is not always reliable and this will not work on every computer)
Also you not know if Outlook is installed ?

CDO is a good option for you


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message ...
Hello again

here is where i am at:

Option Explicit
Sub SaveWorkbook()

Dim USRNM As String
Dim SunDT As String
Dim strFName As String
Dim wsh As Object
Dim fs As Object
Dim DocPath As String
Dim DirString As String
Dim OutApp As Object
Dim OutMail As Object

Set wsh = CreateObject("WScript.Shell")
Set fs = CreateObject("Scripting.FileSystemObject")
DocPath = wsh.SpecialFolders.Item("mydocuments")
DirString = DocPath & "\Time Cards"

With ActiveSheet
USRNM = .Range("EmployeeName").Value
SunDT = Format(.Range("SundayDate").Value, "dd-mmm-yy")

If Trim(USRNM) = "" Or Trim(SunDT) = "" Then
MsgBox "Please add Employee Name and Sunday's Date" & vbLf &
"File not saved!"
Else
If Not fs.FolderExists(DirString) Then
fs.CreateFolder DirString
End If
strFName = "TimeCard " & SunDT & " " & USRNM & ".xls"
.Parent.SaveAs DirString & "\" & strFName
End If

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = strFName
.Body = "Here is the time card for the period starting " & SunDT
.Attachments.Add ActiveWorkbook.FullName
' You can add other files also like this
' .Attachments.Add ("C:\test.txt")
' .Display
' Application.Wait (Now + TimeValue("0:00:02"))
' Application.SendKeys "%S"
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

' ActiveWorkbook.SendMail ", strFName

' Application.Quit
End With

End Sub

i dont want the user to see anything. i want them to press a button and it
is gone. but this code with .send has the security warning. and if i use the
3 lines for .display i end up with the email waiting for the send button to
be pushed. i am thinking that either sendkeys doesnt do what i thought it did
or there is something not working right within those three lines.

help? and thanks in advance.

"Ron de Bruin" wrote:

Good morning

Not working with the SendMail examples
Only for the outlook object model examples that use (Send or Display)

See
http://www.rondebruin.nl/sendmail.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello

slight problem...

ActiveWorkbook.SendMail ", strFName
.Display
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%S"

thats the code i am trying to get to work, but it chokes on the .Display

what does that code do? i understand the rest, but i dont know what this
does or why? it is a 403 property or something not supported.

also do i replace the send mail line or do i need to move it?

"Ron de Bruin" wrote:

Hi DawnTreader

See
http://www.rondebruin.nl/mail/prevent.htm

CDO is a good option

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello Noemi

how did you get the security message to go away?

i assume you mean the one where outlook warns that another program is
attempting to send email.

and someone please answer her question, i could use this as well! :)

thanks for any and all help.

"Noemi" wrote:

Hi
I read Bob Philips response in regards to disabling the security message
when emailing but I wanted to know how we could use the code attached to the
site when not all users get the security message.

I tried the message and on a computer that doesn't get the security message
the workbook would not email.

Any idea's???

Thanks
Noemi

Hello again Ron

so i tried the CDO method. at this point it works great in house. i dont
know about from the outside. i am going to try it at home, but if it doesnt
work then i will be in the same place that i was before.

do you know anyone who is as knowledgable as you are, but in outlook? :)
maybe they can help me get past the menu creation problem.

thanks.

"Ron de Bruin" wrote:

With normal excel menu options you can use .execute
but not with the Send button in the Outlook mail

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message ...
Hello again

here is a thought...

is there a way in vba to specifically hit a button on a toolbar? something
like:

.toolbar(1).button(1).activate

something like that? if there is couldnt we specify the email toolbar and
send button to be activated? i know that is what the sendkeys is supposed to
do, but there is a problem with that method. i have been looking at the %S
and i have noticed that the % is the alt key and the S is intended to hit the
send button. but i dont think sendkeys takes into account that you have to
hold the alt key in order to make that idea work. create an email in outlook
and hold the alt key then while still holding alt and hit the S key. it
sends. alt and then S is nothing. there is no menu in the email creationg
window that has an underlined S.

the real problem here is that i have no menu that has a send option on it.
if there was it would be as simple as using send keys to hit the alt key,
then the menu letter that was underlined and then the underlined key for
send. that would work, and it would always work. currently M$ has screwed up
and made the send function only connected to a button that we cant hit.

any other thoughts on this?

"Ron de Bruin" wrote:

See the note on my site

( SendKeys is not always reliable and this will not work on every computer)
Also you not know if Outlook is installed ?

CDO is a good option for you


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message ...
Hello again

here is where i am at:

Option Explicit
Sub SaveWorkbook()

Dim USRNM As String
Dim SunDT As String
Dim strFName As String
Dim wsh As Object
Dim fs As Object
Dim DocPath As String
Dim DirString As String
Dim OutApp As Object
Dim OutMail As Object

Set wsh = CreateObject("WScript.Shell")
Set fs = CreateObject("Scripting.FileSystemObject")
DocPath = wsh.SpecialFolders.Item("mydocuments")
DirString = DocPath & "\Time Cards"

With ActiveSheet
USRNM = .Range("EmployeeName").Value
SunDT = Format(.Range("SundayDate").Value, "dd-mmm-yy")

If Trim(USRNM) = "" Or Trim(SunDT) = "" Then
MsgBox "Please add Employee Name and Sunday's Date" & vbLf &
"File not saved!"
Else
If Not fs.FolderExists(DirString) Then
fs.CreateFolder DirString
End If
strFName = "TimeCard " & SunDT & " " & USRNM & ".xls"
.Parent.SaveAs DirString & "\" & strFName
End If

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = strFName
.Body = "Here is the time card for the period starting " & SunDT
.Attachments.Add ActiveWorkbook.FullName
' You can add other files also like this
' .Attachments.Add ("C:\test.txt")
' .Display
' Application.Wait (Now + TimeValue("0:00:02"))
' Application.SendKeys "%S"
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

' ActiveWorkbook.SendMail ", strFName

' Application.Quit
End With

End Sub

i dont want the user to see anything. i want them to press a button and it
is gone. but this code with .send has the security warning. and if i use the
3 lines for .display i end up with the email waiting for the send button to
be pushed. i am thinking that either sendkeys doesnt do what i thought it did
or there is something not working right within those three lines.

help? and thanks in advance.

"Ron de Bruin" wrote:

Good morning

Not working with the SendMail examples
Only for the outlook object model examples that use (Send or Display)

See
http://www.rondebruin.nl/sendmail.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello

slight problem...

ActiveWorkbook.SendMail ", strFName
.Display
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%S"

thats the code i am trying to get to work, but it chokes on the .Display

what does that code do? i understand the rest, but i dont know what this
does or why? it is a 403 property or something not supported.

also do i replace the send mail line or do i need to move it?

"Ron de Bruin" wrote:

Hi DawnTreader

See
http://www.rondebruin.nl/mail/prevent.htm

CDO is a good option

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello Noemi

how did you get the security message to go away?

i assume you mean the one where outlook warns that another program is
attempting to send email.

and someone please answer her question, i could use this as well! :)

thanks for any and all help.

"Noemi" wrote:

Hi
I read Bob Philips response in regards to disabling the security message
when emailing but I wanted to know how we could use the code attached to the
site when not all users get the security message.

I tried the message and on a computer that doesn't get the security message
the workbook would not email.

Any idea's???

Thanks
Noemi

http://www.slipstick.com/

--
Regards,
Tom Ogilvy


"DawnTreader" wrote in message
...
Hello again Ron

so i tried the CDO method. at this point it works great in house. i dont
know about from the outside. i am going to try it at home, but if it
doesnt
work then i will be in the same place that i was before.

do you know anyone who is as knowledgable as you are, but in outlook? :)
maybe they can help me get past the menu creation problem.

thanks.

"Ron de Bruin" wrote:

With normal excel menu options you can use .execute
but not with the Send button in the Outlook mail

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello again

here is a thought...

is there a way in vba to specifically hit a button on a toolbar?
something
like:

.toolbar(1).button(1).activate

something like that? if there is couldnt we specify the email toolbar
and
send button to be activated? i know that is what the sendkeys is
supposed to
do, but there is a problem with that method. i have been looking at the
%S
and i have noticed that the % is the alt key and the S is intended to
hit the
send button. but i dont think sendkeys takes into account that you have
to
hold the alt key in order to make that idea work. create an email in
outlook
and hold the alt key then while still holding alt and hit the S key. it
sends. alt and then S is nothing. there is no menu in the email
creationg
window that has an underlined S.

the real problem here is that i have no menu that has a send option on
it.
if there was it would be as simple as using send keys to hit the alt
key,
then the menu letter that was underlined and then the underlined key
for
send. that would work, and it would always work. currently M$ has
screwed up
and made the send function only connected to a button that we cant hit.

any other thoughts on this?

"Ron de Bruin" wrote:

See the note on my site

( SendKeys is not always reliable and this will not work on every
computer)
Also you not know if Outlook is installed ?

CDO is a good option for you


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello again

here is where i am at:

Option Explicit
Sub SaveWorkbook()

Dim USRNM As String
Dim SunDT As String
Dim strFName As String
Dim wsh As Object
Dim fs As Object
Dim DocPath As String
Dim DirString As String
Dim OutApp As Object
Dim OutMail As Object

Set wsh = CreateObject("WScript.Shell")
Set fs = CreateObject("Scripting.FileSystemObject")
DocPath = wsh.SpecialFolders.Item("mydocuments")
DirString = DocPath & "\Time Cards"

With ActiveSheet
USRNM = .Range("EmployeeName").Value
SunDT = Format(.Range("SundayDate").Value, "dd-mmm-yy")

If Trim(USRNM) = "" Or Trim(SunDT) = "" Then
MsgBox "Please add Employee Name and Sunday's Date" &
vbLf &
"File not saved!"
Else
If Not fs.FolderExists(DirString) Then
fs.CreateFolder DirString
End If
strFName = "TimeCard " & SunDT & " " & USRNM & ".xls"
.Parent.SaveAs DirString & "\" & strFName
End If

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = strFName
.Body = "Here is the time card for the period starting "
& SunDT
.Attachments.Add ActiveWorkbook.FullName
' You can add other files also like this
' .Attachments.Add ("C:\test.txt")
' .Display
' Application.Wait (Now + TimeValue("0:00:02"))
' Application.SendKeys "%S"
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

' ActiveWorkbook.SendMail ", strFName

' Application.Quit
End With

End Sub

i dont want the user to see anything. i want them to press a button
and it
is gone. but this code with .send has the security warning. and if i
use the
3 lines for .display i end up with the email waiting for the send
button to
be pushed. i am thinking that either sendkeys doesnt do what i
thought it did
or there is something not working right within those three lines.

help? and thanks in advance.

"Ron de Bruin" wrote:

Good morning

Not working with the SendMail examples
Only for the outlook object model examples that use (Send or
Display)

See
http://www.rondebruin.nl/sendmail.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in
message
...
Hello

slight problem...

ActiveWorkbook.SendMail ", strFName
.Display
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%S"

thats the code i am trying to get to work, but it chokes on the
.Display

what does that code do? i understand the rest, but i dont know
what this
does or why? it is a 403 property or something not supported.

also do i replace the send mail line or do i need to move it?

"Ron de Bruin" wrote:

Hi DawnTreader

See
http://www.rondebruin.nl/mail/prevent.htm

CDO is a good option

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in
message
...
Hello Noemi

how did you get the security message to go away?

i assume you mean the one where outlook warns that another
program is
attempting to send email.

and someone please answer her question, i could use this as
well! :)

thanks for any and all help.

"Noemi" wrote:

Hi
I read Bob Philips response in regards to disabling the
security message
when emailing but I wanted to know how we could use the code
attached to the
site when not all users get the security message.

I tried the message and on a computer that doesn't get the
security message
the workbook would not email.

Any idea's???

Thanks
Noemi

See
http://www.rondebruin.nl/mail/prevent.htm

Read good

Express ClickYes
http://www.contextmagic.com/express-clickyes/

Outlook Redemption
http://www.dimastr.com/redemption/




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message ...
Hello again Ron

so i tried the CDO method. at this point it works great in house. i dont
know about from the outside. i am going to try it at home, but if it doesnt
work then i will be in the same place that i was before.

do you know anyone who is as knowledgable as you are, but in outlook? :)
maybe they can help me get past the menu creation problem.

thanks.

"Ron de Bruin" wrote:

With normal excel menu options you can use .execute
but not with the Send button in the Outlook mail

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message ...
Hello again

here is a thought...

is there a way in vba to specifically hit a button on a toolbar? something
like:

.toolbar(1).button(1).activate

something like that? if there is couldnt we specify the email toolbar and
send button to be activated? i know that is what the sendkeys is supposed to
do, but there is a problem with that method. i have been looking at the %S
and i have noticed that the % is the alt key and the S is intended to hit the
send button. but i dont think sendkeys takes into account that you have to
hold the alt key in order to make that idea work. create an email in outlook
and hold the alt key then while still holding alt and hit the S key. it
sends. alt and then S is nothing. there is no menu in the email creationg
window that has an underlined S.

the real problem here is that i have no menu that has a send option on it.
if there was it would be as simple as using send keys to hit the alt key,
then the menu letter that was underlined and then the underlined key for
send. that would work, and it would always work. currently M$ has screwed up
and made the send function only connected to a button that we cant hit.

any other thoughts on this?

"Ron de Bruin" wrote:

See the note on my site

( SendKeys is not always reliable and this will not work on every computer)
Also you not know if Outlook is installed ?

CDO is a good option for you


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello again

here is where i am at:

Option Explicit
Sub SaveWorkbook()

Dim USRNM As String
Dim SunDT As String
Dim strFName As String
Dim wsh As Object
Dim fs As Object
Dim DocPath As String
Dim DirString As String
Dim OutApp As Object
Dim OutMail As Object

Set wsh = CreateObject("WScript.Shell")
Set fs = CreateObject("Scripting.FileSystemObject")
DocPath = wsh.SpecialFolders.Item("mydocuments")
DirString = DocPath & "\Time Cards"

With ActiveSheet
USRNM = .Range("EmployeeName").Value
SunDT = Format(.Range("SundayDate").Value, "dd-mmm-yy")

If Trim(USRNM) = "" Or Trim(SunDT) = "" Then
MsgBox "Please add Employee Name and Sunday's Date" & vbLf &
"File not saved!"
Else
If Not fs.FolderExists(DirString) Then
fs.CreateFolder DirString
End If
strFName = "TimeCard " & SunDT & " " & USRNM & ".xls"
.Parent.SaveAs DirString & "\" & strFName
End If

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = strFName
.Body = "Here is the time card for the period starting " & SunDT
.Attachments.Add ActiveWorkbook.FullName
' You can add other files also like this
' .Attachments.Add ("C:\test.txt")
' .Display
' Application.Wait (Now + TimeValue("0:00:02"))
' Application.SendKeys "%S"
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

' ActiveWorkbook.SendMail ", strFName

' Application.Quit
End With

End Sub

i dont want the user to see anything. i want them to press a button and it
is gone. but this code with .send has the security warning. and if i use the
3 lines for .display i end up with the email waiting for the send button to
be pushed. i am thinking that either sendkeys doesnt do what i thought it did
or there is something not working right within those three lines.

help? and thanks in advance.

"Ron de Bruin" wrote:

Good morning

Not working with the SendMail examples
Only for the outlook object model examples that use (Send or Display)

See
http://www.rondebruin.nl/sendmail.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello

slight problem...

ActiveWorkbook.SendMail ", strFName
.Display
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%S"

thats the code i am trying to get to work, but it chokes on the .Display

what does that code do? i understand the rest, but i dont know what this
does or why? it is a 403 property or something not supported.

also do i replace the send mail line or do i need to move it?

"Ron de Bruin" wrote:

Hi DawnTreader

See
http://www.rondebruin.nl/mail/prevent.htm

CDO is a good option

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello Noemi

how did you get the security message to go away?

i assume you mean the one where outlook warns that another program is
attempting to send email.

and someone please answer her question, i could use this as well! :)

thanks for any and all help.

"Noemi" wrote:

Hi
I read Bob Philips response in regards to disabling the security message
when emailing but I wanted to know how we could use the code attached to the
site when not all users get the security message.

I tried the message and on a computer that doesn't get the security message
the workbook would not email.

Any idea's???

Thanks
Noemi

Hi DawnTreader

Why do you think a Outlook MVP create

Outlook Redemption
http://www.dimastr.com/redemption/

When he can create a simple button

of that so that a send keys
SendKeys is not always reliable


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message ...
Hello

i dont want to install more software. this is beyond what i am allowed to
do. the CDO code in my sheet wont work from home so it wont work from the
technicians laptops, and no one seems to either be willing to look at what i
propose, or able to understand what i think could work.

i am baffled that no one can tell me how to create a menu item for the email
window from excel and set the parameters of that so that a send keys will
cause it to email itself. can you just give me a clue as to how to add menus
and menu items to an office program window?

there has to be a way to create a menu in the outlook email window, right?
how do you do that? i need to be able to add the menu, name it and set
properties for it if necessary, then add an item from the collection of
commands native to the outlook email window, set the name's underscore, then
change any properties of that and then erase it all.

now if there is a way to do that through code in the outlook email window,
how do we tell excel to do that same thing? there has to be some way to get
the excel code to contact the outlook collection and tell it what to do, isnt
there?

this frustrates me. am i the first person to think of this? should i be
posting this in another group? i was thinking that posting this in the
outlook programming group might get better results. or perhaps the word
group. after all some outlook installs use word for composing emails.

again any an all help is appreciated, but just posting a link to more
software isnt helping. and just so you know, i have now for the third time
THOROUGHLY read the page prevent on your site. i also THOROUGHLY read the
outlook redemption site. redemption is not an option. there is no way i will
be adding software to any of the company computers. click yes is the same
problem.

has anyone used one office program to add a menubar item in another?

"Ron de Bruin" wrote:

See
http://www.rondebruin.nl/mail/prevent.htm

Read good

Express ClickYes
http://www.contextmagic.com/express-clickyes/

Outlook Redemption
http://www.dimastr.com/redemption/




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message ...
Hello again Ron

so i tried the CDO method. at this point it works great in house. i dont
know about from the outside. i am going to try it at home, but if it doesnt
work then i will be in the same place that i was before.

do you know anyone who is as knowledgable as you are, but in outlook? :)
maybe they can help me get past the menu creation problem.

thanks.

"Ron de Bruin" wrote:

With normal excel menu options you can use .execute
but not with the Send button in the Outlook mail

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello again

here is a thought...

is there a way in vba to specifically hit a button on a toolbar? something
like:

.toolbar(1).button(1).activate

something like that? if there is couldnt we specify the email toolbar and
send button to be activated? i know that is what the sendkeys is supposed to
do, but there is a problem with that method. i have been looking at the %S
and i have noticed that the % is the alt key and the S is intended to hit the
send button. but i dont think sendkeys takes into account that you have to
hold the alt key in order to make that idea work. create an email in outlook
and hold the alt key then while still holding alt and hit the S key. it
sends. alt and then S is nothing. there is no menu in the email creationg
window that has an underlined S.

the real problem here is that i have no menu that has a send option on it.
if there was it would be as simple as using send keys to hit the alt key,
then the menu letter that was underlined and then the underlined key for
send. that would work, and it would always work. currently M$ has screwed up
and made the send function only connected to a button that we cant hit.

any other thoughts on this?

"Ron de Bruin" wrote:

See the note on my site

( SendKeys is not always reliable and this will not work on every computer)
Also you not know if Outlook is installed ?

CDO is a good option for you


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello again

here is where i am at:

Option Explicit
Sub SaveWorkbook()

Dim USRNM As String
Dim SunDT As String
Dim strFName As String
Dim wsh As Object
Dim fs As Object
Dim DocPath As String
Dim DirString As String
Dim OutApp As Object
Dim OutMail As Object

Set wsh = CreateObject("WScript.Shell")
Set fs = CreateObject("Scripting.FileSystemObject")
DocPath = wsh.SpecialFolders.Item("mydocuments")
DirString = DocPath & "\Time Cards"

With ActiveSheet
USRNM = .Range("EmployeeName").Value
SunDT = Format(.Range("SundayDate").Value, "dd-mmm-yy")

If Trim(USRNM) = "" Or Trim(SunDT) = "" Then
MsgBox "Please add Employee Name and Sunday's Date" & vbLf &
"File not saved!"
Else
If Not fs.FolderExists(DirString) Then
fs.CreateFolder DirString
End If
strFName = "TimeCard " & SunDT & " " & USRNM & ".xls"
.Parent.SaveAs DirString & "\" & strFName
End If

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = strFName
.Body = "Here is the time card for the period starting " & SunDT
.Attachments.Add ActiveWorkbook.FullName
' You can add other files also like this
' .Attachments.Add ("C:\test.txt")
' .Display
' Application.Wait (Now + TimeValue("0:00:02"))
' Application.SendKeys "%S"
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

' ActiveWorkbook.SendMail ", strFName

' Application.Quit
End With

End Sub

i dont want the user to see anything. i want them to press a button and it
is gone. but this code with .send has the security warning. and if i use the
3 lines for .display i end up with the email waiting for the send button to
be pushed. i am thinking that either sendkeys doesnt do what i thought it did
or there is something not working right within those three lines.

help? and thanks in advance.

"Ron de Bruin" wrote:

Good morning

Not working with the SendMail examples
Only for the outlook object model examples that use (Send or Display)

See
http://www.rondebruin.nl/sendmail.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello

slight problem...

ActiveWorkbook.SendMail ", strFName
.Display
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%S"

thats the code i am trying to get to work, but it chokes on the .Display

what does that code do? i understand the rest, but i dont know what this
does or why? it is a 403 property or something not supported.

also do i replace the send mail line or do i need to move it?

"Ron de Bruin" wrote:

Hi DawnTreader

See
http://www.rondebruin.nl/mail/prevent.htm

CDO is a good option

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello Noemi

how did you get the security message to go away?

i assume you mean the one where outlook warns that another program is
attempting to send email.

and someone please answer her question, i could use this as well! :)

thanks for any and all help.

"Noemi" wrote:

Hi
I read Bob Philips response in regards to disabling the security message
when emailing but I wanted to know how we could use the code attached to the
site when not all users get the security message.

I tried the message and on a computer that doesn't get the security message
the workbook would not email.

Any idea's???

Thanks
Noemi

why is send keys not always reliable?

It send the keystrokes to the active window.
You never know if that is the window that you want.



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message ...
Hello

why is send keys not always reliable?

"Ron de Bruin" wrote:

Hi DawnTreader

Why do you think a Outlook MVP create

Outlook Redemption
http://www.dimastr.com/redemption/

When he can create a simple button

of that so that a send keys
SendKeys is not always reliable


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message ...
Hello

i dont want to install more software. this is beyond what i am allowed to
do. the CDO code in my sheet wont work from home so it wont work from the
technicians laptops, and no one seems to either be willing to look at what i
propose, or able to understand what i think could work.

i am baffled that no one can tell me how to create a menu item for the email
window from excel and set the parameters of that so that a send keys will
cause it to email itself. can you just give me a clue as to how to add menus
and menu items to an office program window?

there has to be a way to create a menu in the outlook email window, right?
how do you do that? i need to be able to add the menu, name it and set
properties for it if necessary, then add an item from the collection of
commands native to the outlook email window, set the name's underscore, then
change any properties of that and then erase it all.

now if there is a way to do that through code in the outlook email window,
how do we tell excel to do that same thing? there has to be some way to get
the excel code to contact the outlook collection and tell it what to do, isnt
there?

this frustrates me. am i the first person to think of this? should i be
posting this in another group? i was thinking that posting this in the
outlook programming group might get better results. or perhaps the word
group. after all some outlook installs use word for composing emails.

again any an all help is appreciated, but just posting a link to more
software isnt helping. and just so you know, i have now for the third time
THOROUGHLY read the page prevent on your site. i also THOROUGHLY read the
outlook redemption site. redemption is not an option. there is no way i will
be adding software to any of the company computers. click yes is the same
problem.

has anyone used one office program to add a menubar item in another?

"Ron de Bruin" wrote:

See
http://www.rondebruin.nl/mail/prevent.htm

Read good

Express ClickYes
http://www.contextmagic.com/express-clickyes/

Outlook Redemption
http://www.dimastr.com/redemption/




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello again Ron

so i tried the CDO method. at this point it works great in house. i dont
know about from the outside. i am going to try it at home, but if it doesnt
work then i will be in the same place that i was before.

do you know anyone who is as knowledgable as you are, but in outlook? :)
maybe they can help me get past the menu creation problem.

thanks.

"Ron de Bruin" wrote:

With normal excel menu options you can use .execute
but not with the Send button in the Outlook mail

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello again

here is a thought...

is there a way in vba to specifically hit a button on a toolbar? something
like:

.toolbar(1).button(1).activate

something like that? if there is couldnt we specify the email toolbar and
send button to be activated? i know that is what the sendkeys is supposed to
do, but there is a problem with that method. i have been looking at the %S
and i have noticed that the % is the alt key and the S is intended to hit the
send button. but i dont think sendkeys takes into account that you have to
hold the alt key in order to make that idea work. create an email in outlook
and hold the alt key then while still holding alt and hit the S key. it
sends. alt and then S is nothing. there is no menu in the email creationg
window that has an underlined S.

the real problem here is that i have no menu that has a send option on it.
if there was it would be as simple as using send keys to hit the alt key,
then the menu letter that was underlined and then the underlined key for
send. that would work, and it would always work. currently M$ has screwed up
and made the send function only connected to a button that we cant hit.

any other thoughts on this?

"Ron de Bruin" wrote:

See the note on my site

( SendKeys is not always reliable and this will not work on every computer)
Also you not know if Outlook is installed ?

CDO is a good option for you


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello again

here is where i am at:

Option Explicit
Sub SaveWorkbook()

Dim USRNM As String
Dim SunDT As String
Dim strFName As String
Dim wsh As Object
Dim fs As Object
Dim DocPath As String
Dim DirString As String
Dim OutApp As Object
Dim OutMail As Object

Set wsh = CreateObject("WScript.Shell")
Set fs = CreateObject("Scripting.FileSystemObject")
DocPath = wsh.SpecialFolders.Item("mydocuments")
DirString = DocPath & "\Time Cards"

With ActiveSheet
USRNM = .Range("EmployeeName").Value
SunDT = Format(.Range("SundayDate").Value, "dd-mmm-yy")

If Trim(USRNM) = "" Or Trim(SunDT) = "" Then
MsgBox "Please add Employee Name and Sunday's Date" & vbLf &
"File not saved!"
Else
If Not fs.FolderExists(DirString) Then
fs.CreateFolder DirString
End If
strFName = "TimeCard " & SunDT & " " & USRNM & ".xls"
.Parent.SaveAs DirString & "\" & strFName
End If

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = strFName
.Body = "Here is the time card for the period starting " & SunDT
.Attachments.Add ActiveWorkbook.FullName
' You can add other files also like this
' .Attachments.Add ("C:\test.txt")
' .Display
' Application.Wait (Now + TimeValue("0:00:02"))
' Application.SendKeys "%S"
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

' ActiveWorkbook.SendMail ", strFName

' Application.Quit
End With

End Sub

i dont want the user to see anything. i want them to press a button and it
is gone. but this code with .send has the security warning. and if i use the
3 lines for .display i end up with the email waiting for the send button to
be pushed. i am thinking that either sendkeys doesnt do what i thought it did
or there is something not working right within those three lines.

help? and thanks in advance.

"Ron de Bruin" wrote:

Good morning

Not working with the SendMail examples
Only for the outlook object model examples that use (Send or Display)

See
http://www.rondebruin.nl/sendmail.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello

slight problem...

ActiveWorkbook.SendMail ", strFName
.Display
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%S"

thats the code i am trying to get to work, but it chokes on the .Display

what does that code do? i understand the rest, but i dont know what this
does or why? it is a 403 property or something not supported.

also do i replace the send mail line or do i need to move it?

"Ron de Bruin" wrote:

Hi DawnTreader

See
http://www.rondebruin.nl/mail/prevent.htm

CDO is a good option

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello Noemi

how did you get the security message to go away?

i assume you mean the one where outlook warns that another program is
attempting to send email.

and someone please answer her question, i could use this as well! :)

thanks for any and all help.

"Noemi" wrote:

Hi
I read Bob Philips response in regards to disabling the security message
when emailing but I wanted to know how we could use the code attached to the
site when not all users get the security message.

I tried the message and on a computer that doesn't get the security message
the workbook would not email.

Any idea's???

Hello again Ron.

now that i am a little bit calmer about this thread i figured i would give
this another try. i just wrote this in another thread:

the reason i want to use the menu bar is to allow a send keys to activate
the item i want to use. during my working out this code i have noticed one
thing about the send key function that isnt mentioned anywhere that i have
seen. the command i was given went like this:

Application.SendKeys "%S"

the "%" is an alt key "hit". a "hit" not a "hold". this is an important
thing to note. go to an outlook email window and hold the alt key and then
hit "S". you will get a message saying you need to add an address. that is
because the holding of the altkey and hitting "S" is tapping the "Send"
button on the toolbar that is visible. however, the % symbol in the send key
function is not a hold. it is a "hit" or a tap. in the same outlook email
window tap the alt key, what happens? the file menu becomes highlighted.

and since there is no menu that has an underlined "S" nothing happens. so
send keys does work, just not in the way that i think most people expect.
this is where creating my own menu and menu item comes in. if i create a menu
in the outlook email window with excel code, then use a sendkeys command to
to use the menu, and the menu item, i should be able to email the spreadsheet
that the technicians need to send me, without any user intervention.

so the question is can i create the menu?

"Ron de Bruin" wrote:

why is send keys not always reliable?

It send the keystrokes to the active window.
You never know if that is the window that you want.



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message ...
Hello

why is send keys not always reliable?

"Ron de Bruin" wrote:

Hi DawnTreader

Why do you think a Outlook MVP create

Outlook Redemption
http://www.dimastr.com/redemption/

When he can create a simple button

of that so that a send keys
SendKeys is not always reliable


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message ...
Hello

i dont want to install more software. this is beyond what i am allowed to
do. the CDO code in my sheet wont work from home so it wont work from the
technicians laptops, and no one seems to either be willing to look at what i
propose, or able to understand what i think could work.

i am baffled that no one can tell me how to create a menu item for the email
window from excel and set the parameters of that so that a send keys will
cause it to email itself. can you just give me a clue as to how to add menus
and menu items to an office program window?

there has to be a way to create a menu in the outlook email window, right?
how do you do that? i need to be able to add the menu, name it and set
properties for it if necessary, then add an item from the collection of
commands native to the outlook email window, set the name's underscore, then
change any properties of that and then erase it all.

now if there is a way to do that through code in the outlook email window,
how do we tell excel to do that same thing? there has to be some way to get
the excel code to contact the outlook collection and tell it what to do, isnt
there?

this frustrates me. am i the first person to think of this? should i be
posting this in another group? i was thinking that posting this in the
outlook programming group might get better results. or perhaps the word
group. after all some outlook installs use word for composing emails.

again any an all help is appreciated, but just posting a link to more
software isnt helping. and just so you know, i have now for the third time
THOROUGHLY read the page prevent on your site. i also THOROUGHLY read the
outlook redemption site. redemption is not an option. there is no way i will
be adding software to any of the company computers. click yes is the same
problem.

has anyone used one office program to add a menubar item in another?

"Ron de Bruin" wrote:

See
http://www.rondebruin.nl/mail/prevent.htm

Read good

Express ClickYes
http://www.contextmagic.com/express-clickyes/

Outlook Redemption
http://www.dimastr.com/redemption/




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello again Ron

so i tried the CDO method. at this point it works great in house. i dont
know about from the outside. i am going to try it at home, but if it doesnt
work then i will be in the same place that i was before.

do you know anyone who is as knowledgable as you are, but in outlook? :)
maybe they can help me get past the menu creation problem.

thanks.

"Ron de Bruin" wrote:

With normal excel menu options you can use .execute
but not with the Send button in the Outlook mail

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello again

here is a thought...

is there a way in vba to specifically hit a button on a toolbar? something
like:

.toolbar(1).button(1).activate

something like that? if there is couldnt we specify the email toolbar and
send button to be activated? i know that is what the sendkeys is supposed to
do, but there is a problem with that method. i have been looking at the %S
and i have noticed that the % is the alt key and the S is intended to hit the
send button. but i dont think sendkeys takes into account that you have to
hold the alt key in order to make that idea work. create an email in outlook
and hold the alt key then while still holding alt and hit the S key. it
sends. alt and then S is nothing. there is no menu in the email creationg
window that has an underlined S.

the real problem here is that i have no menu that has a send option on it.
if there was it would be as simple as using send keys to hit the alt key,
then the menu letter that was underlined and then the underlined key for
send. that would work, and it would always work. currently M$ has screwed up
and made the send function only connected to a button that we cant hit.

any other thoughts on this?

"Ron de Bruin" wrote:

See the note on my site

( SendKeys is not always reliable and this will not work on every computer)
Also you not know if Outlook is installed ?

CDO is a good option for you


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello again

here is where i am at:

Option Explicit
Sub SaveWorkbook()

Dim USRNM As String
Dim SunDT As String
Dim strFName As String
Dim wsh As Object
Dim fs As Object
Dim DocPath As String
Dim DirString As String
Dim OutApp As Object
Dim OutMail As Object

Set wsh = CreateObject("WScript.Shell")
Set fs = CreateObject("Scripting.FileSystemObject")
DocPath = wsh.SpecialFolders.Item("mydocuments")
DirString = DocPath & "\Time Cards"

With ActiveSheet
USRNM = .Range("EmployeeName").Value
SunDT = Format(.Range("SundayDate").Value, "dd-mmm-yy")

If Trim(USRNM) = "" Or Trim(SunDT) = "" Then
MsgBox "Please add Employee Name and Sunday's Date" & vbLf &
"File not saved!"
Else
If Not fs.FolderExists(DirString) Then
fs.CreateFolder DirString
End If
strFName = "TimeCard " & SunDT & " " & USRNM & ".xls"
.Parent.SaveAs DirString & "\" & strFName
End If

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = strFName
.Body = "Here is the time card for the period starting " & SunDT
.Attachments.Add ActiveWorkbook.FullName
' You can add other files also like this
' .Attachments.Add ("C:\test.txt")
' .Display
' Application.Wait (Now + TimeValue("0:00:02"))
' Application.SendKeys "%S"
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

' ActiveWorkbook.SendMail ", strFName

' Application.Quit
End With

End Sub

i dont want the user to see anything. i want them to press a button and it
is gone. but this code with .send has the security warning. and if i use the
3 lines for .display i end up with the email waiting for the send button to
be pushed. i am thinking that either sendkeys doesnt do what i thought it did
or there is something not working right within those three lines.

help? and thanks in advance.

"Ron de Bruin" wrote:

Good morning

Not working with the SendMail examples
Only for the outlook object model examples that use (Send or Display)

See
http://www.rondebruin.nl/sendmail.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello

slight problem...

ActiveWorkbook.SendMail ", strFName
.Display
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%S"

thats the code i am trying to get to work, but it chokes on the .Display

what does that code do? i understand the rest, but i dont know what this
does or why? it is a 403 property or something not supported.

also do i replace the send mail line or do i need to move it?

"Ron de Bruin" wrote:

Hi DawnTreader

See
http://www.rondebruin.nl/mail/prevent.htm

CDO is a good option

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello Noemi

Hmmmm.

i just did a little reading. i found that there are 2 kinds of send keys.

excel and VBA

the VBA uses brackets to enclose a key to be hit while another key is held.
so:

application.sendkeys "%(S)" hits the send button on the toolbar and does
almost exactly what i want it to do. i still need to disable the spellcheck,
or sendkeys to cause it to ignore the mispeltwords.



"DawnTreader" wrote:

Hello again Ron.

now that i am a little bit calmer about this thread i figured i would give
this another try. i just wrote this in another thread:

the reason i want to use the menu bar is to allow a send keys to activate
the item i want to use. during my working out this code i have noticed one
thing about the send key function that isnt mentioned anywhere that i have
seen. the command i was given went like this:

Application.SendKeys "%S"

the "%" is an alt key "hit". a "hit" not a "hold". this is an important
thing to note. go to an outlook email window and hold the alt key and then
hit "S". you will get a message saying you need to add an address. that is
because the holding of the altkey and hitting "S" is tapping the "Send"
button on the toolbar that is visible. however, the % symbol in the send key
function is not a hold. it is a "hit" or a tap. in the same outlook email
window tap the alt key, what happens? the file menu becomes highlighted.

and since there is no menu that has an underlined "S" nothing happens. so
send keys does work, just not in the way that i think most people expect.
this is where creating my own menu and menu item comes in. if i create a menu
in the outlook email window with excel code, then use a sendkeys command to
to use the menu, and the menu item, i should be able to email the spreadsheet
that the technicians need to send me, without any user intervention.

so the question is can i create the menu?

"Ron de Bruin" wrote:

why is send keys not always reliable?

It send the keystrokes to the active window.
You never know if that is the window that you want.



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message ...
Hello

why is send keys not always reliable?

"Ron de Bruin" wrote:

Hi DawnTreader

Why do you think a Outlook MVP create

Outlook Redemption
http://www.dimastr.com/redemption/

When he can create a simple button

of that so that a send keys
SendKeys is not always reliable


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message ...
Hello

i dont want to install more software. this is beyond what i am allowed to
do. the CDO code in my sheet wont work from home so it wont work from the
technicians laptops, and no one seems to either be willing to look at what i
propose, or able to understand what i think could work.

i am baffled that no one can tell me how to create a menu item for the email
window from excel and set the parameters of that so that a send keys will
cause it to email itself. can you just give me a clue as to how to add menus
and menu items to an office program window?

there has to be a way to create a menu in the outlook email window, right?
how do you do that? i need to be able to add the menu, name it and set
properties for it if necessary, then add an item from the collection of
commands native to the outlook email window, set the name's underscore, then
change any properties of that and then erase it all.

now if there is a way to do that through code in the outlook email window,
how do we tell excel to do that same thing? there has to be some way to get
the excel code to contact the outlook collection and tell it what to do, isnt
there?

this frustrates me. am i the first person to think of this? should i be
posting this in another group? i was thinking that posting this in the
outlook programming group might get better results. or perhaps the word
group. after all some outlook installs use word for composing emails.

again any an all help is appreciated, but just posting a link to more
software isnt helping. and just so you know, i have now for the third time
THOROUGHLY read the page prevent on your site. i also THOROUGHLY read the
outlook redemption site. redemption is not an option. there is no way i will
be adding software to any of the company computers. click yes is the same
problem.

has anyone used one office program to add a menubar item in another?

"Ron de Bruin" wrote:

See
http://www.rondebruin.nl/mail/prevent.htm

Read good

Express ClickYes
http://www.contextmagic.com/express-clickyes/

Outlook Redemption
http://www.dimastr.com/redemption/




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello again Ron

so i tried the CDO method. at this point it works great in house. i dont
know about from the outside. i am going to try it at home, but if it doesnt
work then i will be in the same place that i was before.

do you know anyone who is as knowledgable as you are, but in outlook? :)
maybe they can help me get past the menu creation problem.

thanks.

"Ron de Bruin" wrote:

With normal excel menu options you can use .execute
but not with the Send button in the Outlook mail

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello again

here is a thought...

is there a way in vba to specifically hit a button on a toolbar? something
like:

.toolbar(1).button(1).activate

something like that? if there is couldnt we specify the email toolbar and
send button to be activated? i know that is what the sendkeys is supposed to
do, but there is a problem with that method. i have been looking at the %S
and i have noticed that the % is the alt key and the S is intended to hit the
send button. but i dont think sendkeys takes into account that you have to
hold the alt key in order to make that idea work. create an email in outlook
and hold the alt key then while still holding alt and hit the S key. it
sends. alt and then S is nothing. there is no menu in the email creationg
window that has an underlined S.

the real problem here is that i have no menu that has a send option on it.
if there was it would be as simple as using send keys to hit the alt key,
then the menu letter that was underlined and then the underlined key for
send. that would work, and it would always work. currently M$ has screwed up
and made the send function only connected to a button that we cant hit.

any other thoughts on this?

"Ron de Bruin" wrote:

See the note on my site

( SendKeys is not always reliable and this will not work on every computer)
Also you not know if Outlook is installed ?

CDO is a good option for you


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello again

here is where i am at:

Option Explicit
Sub SaveWorkbook()

Dim USRNM As String
Dim SunDT As String
Dim strFName As String
Dim wsh As Object
Dim fs As Object
Dim DocPath As String
Dim DirString As String
Dim OutApp As Object
Dim OutMail As Object

Set wsh = CreateObject("WScript.Shell")
Set fs = CreateObject("Scripting.FileSystemObject")
DocPath = wsh.SpecialFolders.Item("mydocuments")
DirString = DocPath & "\Time Cards"

With ActiveSheet
USRNM = .Range("EmployeeName").Value
SunDT = Format(.Range("SundayDate").Value, "dd-mmm-yy")

If Trim(USRNM) = "" Or Trim(SunDT) = "" Then
MsgBox "Please add Employee Name and Sunday's Date" & vbLf &
"File not saved!"
Else
If Not fs.FolderExists(DirString) Then
fs.CreateFolder DirString
End If
strFName = "TimeCard " & SunDT & " " & USRNM & ".xls"
.Parent.SaveAs DirString & "\" & strFName
End If

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = strFName
.Body = "Here is the time card for the period starting " & SunDT
.Attachments.Add ActiveWorkbook.FullName
' You can add other files also like this
' .Attachments.Add ("C:\test.txt")
' .Display
' Application.Wait (Now + TimeValue("0:00:02"))
' Application.SendKeys "%S"
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

' ActiveWorkbook.SendMail ", strFName

' Application.Quit
End With

End Sub

i dont want the user to see anything. i want them to press a button and it
is gone. but this code with .send has the security warning. and if i use the
3 lines for .display i end up with the email waiting for the send button to
be pushed. i am thinking that either sendkeys doesnt do what i thought it did
or there is something not working right within those three lines.

help? and thanks in advance.

"Ron de Bruin" wrote:

Good morning

Not working with the SendMail examples
Only for the outlook object model examples that use (Send or Display)

See
http://www.rondebruin.nl/sendmail.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message
...
Hello

slight problem...