VBA Code application.dialogs xldialogsendmail .show _ 1

[alchohaz]

Hello all

I am trying to get some assistance on the code "application.dialogs xldialogsendmail .show _" i am using this to send an email from Excel.

I have set up mu code to run this as required but now i am looking for something that will set a cell value as current time and date when the email has been sucesfully sent.

I am in aposition where i am gett8ing the time and date stamp where i want it BUT, not only does it stamp the time when i send the email BUT when i cancell sending the email aswell. Is there anyway i can get the code to only time stamp when an email has been sent?

Sub Approved()
'Set variable for subject and prefix with approval
Dim WSName1 As String, CName1 As String, savename1 As String
Dim Subject As String
'SET Filename
WSName1 = "Header"
CName1 = "CR_Num"
savename1 = Sheets(WSName1).Range(CName1).Text
Subject = "Approved & Ready ~" & savename1 & " " & "Assessment.xlsx"
'Send email with
Application.Dialogs(xlDialogSendMail).Show _
arg1:="test@abc.org;", _
arg2:=Subject

    Range("Completed").Value = FormatDateTime(Now, vbGeneralDate)

End Sub

Thanks in advance for your assistance.

 

[SkipVought]

hi,

The Format function returns a STRING, not a Datet/Time value...

    With Range("Completed")
       .Value = Now[b]
'for instance[/b]
       .NumberFormat = "yyyy-mm-dd hh:mm:ss"[b]
'but knock yourself out with any other format you would like[/b]
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

If Application.Dialogs(xlDialogSendMail).Show _
arg1:="test@abc.org;", _
arg2:=Subject = True Then
    Range("Completed").Value = FormatDateTime(Now, vbGeneralDate)
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[alchohaz]

Thank you both for your help, PHV you are thinking more down the lines that i am looking for, but the code was not accepted - maybe it's me, i am a bit of hacker when it comes to VBA and just feeling my way around

Basically i only want the time stamp in the cell IF the email is sent, and NOT if the cancel button on the dialogue box is clicked..

Thanks again
Al

 

[PHV]

Sorry, try this instead:

If Application.Dialogs(xlDialogSendMail).Show("test@abc.org;", Subject) = True Then
    Range("Completed").Value = FormatDateTime(Now, vbGeneralDate)
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[alchohaz]

Perfect - thanks very much for your help !!