Solved

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

Posted on 2002-05-31
9
1,961 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 4

Expert Comment

by:Monchanger
ID: 7048372
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
ID: 7048374
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
ID: 7048619
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:Monchanger
ID: 7048642
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
 

Author Comment

by:mathes
ID: 7049668
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
ID: 7049728
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
ID: 7050974
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
ID: 7051007
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
ID: 7051103
Hi there,

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

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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 walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…

724 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