Solved: Excel; First show Email before sending

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Willum

Thread Starter
Joined
Jun 12, 2008
Messages
137
Hi all,

I have a question,
I use the following code to send a Excel workbook via Email.

Code:
Private Sub Mail_workbook_1_Click()
'Working in 97-2010
    Dim wb As Workbook
    Dim I As Long

    Set wb = ActiveWorkbook

    If Val(Application.Version) >= 12 Then
        If wb.FileFormat = 51 And wb.HasVBProject = True Then
            MsgBox "There is VBA code in this xlsx file, there will" & vbNewLine & _
                   "be no VBA code in the file you send. Save the" & vbNewLine & _
                   "file first as xlsm and then try the macro again.", vbInformation
            Exit Sub
        End If
    End If

    On Error Resume Next
    For I = 1 To 3
        wb.SendMail "[email protected]", _
                    "Subject"
        If Err.Number = 0 Then Exit For
    Next I
    On Error GoTo 0
End Sub
It does the trick for so far, but it sends the e-mail directly, without showing the E-mail itself first (only a notifications shows up which tells you some program is trying to send an email).
I would like to manually select Send in the E-mailmessage, so our customers are sure that the e-mail is send correctly.

How do I change this?

An other question, the Subject of the message is now "Subject" how could I 'attach' a value from cell B9 to it?

It would be pretty simple I guess, but I can't find the answers.

And last and also least important.. It isn't possible to give the user the possibility to send the e-mail through gmail or hotmail? (I know FireFox has the ability to select with which e-mailclient you want to send the e-mail, but as far as I know, that's not possible with Excel)
 

Keebellah

Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
First Name
Hans
I don't knwo is this works but try wb.DisplayMail instead.

You can use a "Mail To:" option which uses the default mail client the user has, but I haven't been able to include attachmentes with this option.
I suggest a good Google search will give you some more hints.
 

Willum

Thread Starter
Joined
Jun 12, 2008
Messages
137
Hi Hans,

thanks for your quick reply.

I just made this:

Code:
Private Sub Mail_workbook_1_Click()
'Working in 97-2010
    Dim wb As Workbook
    Dim I As Long

    Set wb = ActiveWorkbook

    If Val(Application.Version) >= 12 Then
        If wb.FileFormat = 51 And wb.HasVBProject = True Then
            MsgBox "There is VBA code in this xlsx file, there will" & vbNewLine & _
                   "be no VBA code in the file you send. Save the" & vbNewLine & _
                   "file first as xlsm and then try the macro again.", vbInformation
            Exit Sub
        End If
    End If

    On Error Resume Next
    For I = 1 To 3
        wb.SendMail "[email protected]", _
                    "Subject " & Range("B9").Value
        If Err.Number = 0 Then Exit For
    Next I
    On Error GoTo 0
End Sub
I searched google a lot, but alot of answers are not visible, unless you take an account etc.

The attach B9 in the subject works now.
This code comes from rondebruin.nl
It does send the file in the e-mail.

The DisplayMail I allready tried, doesn't work (It doesn't do anything then).

If another piece of code does the trick also, that's fine too.. It doesn't have to be this particular code, I just found it and it's not so complicated / full of functions I don't need.
 

Keebellah

Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
First Name
Hans
This code come's form Rn's site too:

Code:
Sub SendUpdate()
    Call MailData("subject string", "status file updated", "[email protected]")
End Sub

Function MailData(mSubject As String, mMessage As String, Sendto As String, Optional CCto As String)
Dim eSubject As String, EBody As String
Dim app As Object, Itm As Variant
Set app = CreateObject("Outlook.Application")
Set Itm = app.CreateItem(0)
With Itm
.Subject = mSubject
.to = Sendto
 If Not IsMissing(CCto) Then .CC = CCto
.Body = mMessage
.Save           ' This property is used when you want to saves mail to the Concept folder
 .Display      ' This property is used when you want to display before sending
' .Send         ' This property is used if you want to send without verification
' .Attachments.Add (Filename) ' Must be complete path'and filename if you require an attachment to be included
End With
Set app = Nothing
Set Itm = Nothing
End Function
Try the sendUpade and you'll see it work.

All you will need to do is pass the parameters and edit accordingly. It works!
 

Willum

Thread Starter
Joined
Jun 12, 2008
Messages
137
Hans,
Thanks for your input.
I'll try to combine both, so the file is attached automatically.
 

Willum

Thread Starter
Joined
Jun 12, 2008
Messages
137
Got it working

Code:
Sub SendUpdate()     Call MailData("subject string", "status file updated", "[email protected]") End Sub  Function MailData(mSubject As String, mMessage As String, Sendto As String, Optional CCto As String) Dim eSubject As String, EBody As String Dim app As Object, Itm As Variant Set app = CreateObject("Outlook.Application") Set Itm = app.CreateItem(0) With Itm .Subject = mSubject .to = Sendto  If Not IsMissing(CCto) Then .CC = CCto .Body = mMessage .Save           ' This property is used when you want to saves mail to the Concept folder  .Display      ' This property is used when you want to display before sending ' .Send         ' This property is used if you want to send without verification .Attachments.Add ActiveWorkbook.FullName End With Set app = Nothing Set Itm = Nothing End Function
Notice the .Attachments.Add ActiveWorkbook.FullName
This makes the active workbook being added into the E-mail.
Thanks Hans for your help!
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top