Excel 2002; VBA macro; problem with mailto, blind copy and carbon copy

Hi experts,

with the help of the following code I try to lauch my e-mail client.

Private Declare Function ShellExecute Lib "Shell32.dll" _
    Alias "ShellExecuteA" (ByVal hWnd As Long, _
    ByVal lpOperation As String, ByVal lpFile As String, _
    ByVal lpParameters As String, ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) As Long

Private Sub Mail(eMail As String, Optional Subject As String, _
    Optional Body As String, Optional cc As String, Optional bc As
String)
   
  Call ShellExecute(0&, "Open", "mailto:" + eMail + _
     "?Subject=" + Subject + "&Body=" + Body, cc + "CC", bc + "BC",1)
        End Sub
       
Private Sub CommandButton1_Click()
Dim C As Range
Dim eMail$, Subject$, Body$
Dim Rw%, Cl%
    eMail = "hans@aol.com"
    Subject = "Excel-Datei"
    Set C = Range("A1:A5")
    cc = "me@aol.com"
    bc = "you@aol.com"
    Call Mail(eMail, Subject, Body, cc, bc)
End Sub

Unfortunately this code does not work properly.
My e-mail client is launched, but the created e-mail has neither a carbon copy nor a blind copy.

Can you please tell me how I can fix this problem?

Please note that I don't want a MS Outlook specific solution.

mathesAsked:
Who is Participating?
 
MonchangerConnect With a Mentor Commented:
The argument Body is wrong (says only "B")

Private Sub Mail(ByVal eMail As String, Optional ByVal Subject As String, Optional ByVal B, Optional ByVal cc As String, Optional ByVal bc As String)

to:

Private Sub Mail(ByVal eMail As String, Optional ByVal Subject As String, Optional ByVal Body, Optional ByVal cc As String, Optional ByVal bc As String)

To avoid errors like this, add "Option Explicit" as the first line of code.
0
 
MonchangerCommented:
I used the following URL to re-write the shellexecute command

http://developer.netscape.com/viewsource/husted_mailto/mailto.html


Private Sub Mail(eMail As String, Optional Subject As String, Optional Body As String, Optional cc As String, Optional bc As String)
   
    Dim strMailto As String
    strMailto = "mailto:" & eMail & _
          "?cc=" & cc & _
          "&bcc=" & bc & _
          "&subject=" & Subject & _
          "&body=" & Body
     
    Call ShellExecute(0&, "Open", strMailto, "", "", 1)
End Sub

PS - Try to get into the habbit of using the ampersand (&) when combinining strings.
0
 
MonchangerCommented:
Hmmm .... My wording was pretty bad there. Let's try it this way:

I used the following URL to re-write the *argument for the* shellexecute command.

That's better.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
mathesAuthor Commented:
Hi there,

thank you for your input,

I meanwhile changed my code like this:

Private Declare Function ShellExecute Lib "Shell32.dll" _
    Alias "ShellExecuteA" (ByVal hWnd As Long, _
    ByVal lpOperation As String, ByVal lpFile As String, _
    ByVal lpParameters As String, ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) As Long


Private Sub Mail(eMail As String, Optional Subject As String, Optional Body As String, Optional _
   cc As String, Optional bc As String)
   
   Dim strMailto As String
   
   strMailto = "mailto:" & eMail & _
         "?cc=" & cc & _
         "&bcc=" & bc & _
         "&subject=" & Subject & _
         "&body=" & Body
     
   Call ShellExecute(0&, "Open", strMailto, "", "", 1)
End Sub


Private Sub CommandButton1_Click()
  Call Mail(strMailto)
End Sub

There is still a problem. I don't understand how I can assign values
to body, subject, email, blindcopy and carboncopy.

Actually I need these values in my e-mail:

   email = "hans@aol.com"
   Subject = "Excel-Datei"
   body = Sheet1.Range("A1:A5")
   cc = "me@aol.com"
   bc = "you@aol.com"
   

Can you please tell me, how I can accomplish this?
0
 
MonchangerCommented:
You need to add the rest of the arguments:
Call Mail(strMailto, strSubject, strBody, strCC, strBC)
(strXXX - depend on where you get those values from)


In case you want to understand what you're doing:

The definition of the Mail subprocedure appears in the line:
> Private Sub Mail(eMail As String, Optional Subject As String, Optional Body As String, Optional cc As String, Optional bc As String)

What it means is that to call the subprocedure, you use "Call(Main (....))" as you did.

The rest of the line ("eMail As String ....") defines the arguments of the subprocedure. These are values which you pass to the subprocedure so that it knows what to do.

The subprocedure uses the names of the arguments ("eMail", "Subject", "Body" etc) like variables.

By passing only the first argument "strMailto" you were simply telling the subprocedure what the email you want to send to.

Also, the "Optional" clauses in the subprocedures definition states that you do not *have to* give these values. But if you don't, they simply equal "" (a blank string)


I believe the VBA help should contain this information. This is part of the basic stuff you need to understand for programming.
0
 
mathesAuthor Commented:
Hi there,

thankl you again for your input.

Meanwhile I have this code:

Private Declare Function ShellExecute Lib "Shell32.dll" _
    Alias "ShellExecuteA" (ByVal hWnd As Long, _
    ByVal lpOperation As String, ByVal lpFile As String, _
    ByVal lpParameters As String, ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) As Long

Private Sub Mail(eMail As String, Optional Subject As String, _
Optional Body As String, Optional cc As String, Optional bc As String)
 
   Dim strMailto As String
   strMailto = "mailto:" & eMail & _
         "?cc=" & cc & _
         "&bcc=" & bc & _
         "&subject=" & Subject & _
         "&body=" & Body
     
   Call ShellExecute(0&, "Open", strMailto, "", "", 1)
   End Sub
   

Private Sub CommandButton1_Click()
 Call Mail(strMailto, strSubject, strBody, strCC, strBC)
End Sub

The line

Call Mail(strMailto, strSubject, strBody, strCC, strBC)

produces an error:

Invalid argument type ByRef

Can you please tell me, how I can solve this problem?
0
 
MonchangerCommented:
I don't think this should matter, but try to change this line:
> Private Sub Mail(eMail As String, Optional Subject As String, Optional Body As String, Optional cc As String, Optional bc As String

to :

Private Sub Mail(ByVal eMail As String, Optional ByVal Subject As String, Optional ByVal Body As String, Optional ByVal cc As String, Optional ByVal bc As String)

(added the "ByVal" statement before all parameters)

If this isn't it, how are the variables you use (strMailto, strSubject, strBody, strCC, strBC) defined ? They should be strings :
dim strMailto as string
dim strSubject as string
dim strBody as string
dim strCC as string
dim strBC as string
0
 
mathesAuthor Commented:
Hi there,

thank you again for your input. I certainly would like to understand what I am doing.
This is the reason why I ask again. I have made some progress and I have now a code
that performs almost exactly what I would like to accomplish.

The only problem is that the body remains empty when my E-mail client is launched.
The other components (subject, blind copy, carbon copy, recipient) have exactly the values
which I would like them to have.

Can you please tell me how I can fix the problem with the E-Mail body?

Here is the source code:



Private Declare Function ShellExecute Lib "Shell32.dll" _
Alias "ShellExecuteA" (ByVal hWnd As Long, _
ByVal lpOperation As String, ByVal lpFile As String, _
ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long


Dim strMailto As String
Dim strSubject As String
Dim strBody As String
Dim strCC As String
Dim strBC As String



Private Sub Mail(ByVal eMail As String, Optional ByVal Subject As String, _
Optional ByVal B, Optional ByVal cc As String, Optional ByVal bc As String)

 strMailto = "mailto:" & eMail & _
"?cc=" & cc & _
"&bcc=" & bc & _
"&subject=" & Subject & _
"&body=" & Body
 
   Call ShellExecute(0&, "Open", strMailto, "", "", 1)
End Sub




Private Sub CommandButton1_Click()
strSubject = "Testsubject"
strBody = "Testbody"
strCC = "carboncopy@aol.com"
strBC = "blindcopy@aol.com"
strMailto = "myfriend@aol.com"

  Call Mail(strMailto, strSubject, strBody, strCC, strBC)
End Sub




0
 
mathesAuthor Commented:
Hi there,

thank you very much for your help. This is exactly what I was looking for.
0
All Courses

From novice to tech pro — start learning today.