1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel; First show Email before sending

Discussion in 'Business Applications' started by Willum, Oct 16, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. Willum

    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)
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    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.
     
  3. Willum

    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.
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    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!
     
  5. Willum

    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.
     
  6. Willum

    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!
     
  7. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1072883

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice