Solved

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

Posted on 2002-05-31
9
1,745 Views
Last Modified: 2007-11-27
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.

0
Comment
Question by:mathes
  • 5
  • 4
9 Comments
 
LVL 4

Expert Comment

by:Monchanger
Comment Utility
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
 
LVL 4

Expert Comment

by:Monchanger
Comment Utility
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
 

Author Comment

by:mathes
Comment Utility
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
 
LVL 4

Expert Comment

by:Monchanger
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:mathes
Comment Utility
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
 
LVL 4

Expert Comment

by:Monchanger
Comment Utility
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
 

Author Comment

by:mathes
Comment Utility
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
 
LVL 4

Accepted Solution

by:
Monchanger earned 100 total points
Comment Utility
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
 

Author Comment

by:mathes
Comment Utility
Hi there,

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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction Perhaps more familiar to developers who primarily use VBScript than to developers who tend to work only with Microsoft Office and Visual Basic for Applications (VBA), the Dictionary is a powerful and versatile class, and is useful …
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now