Solved

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

Posted on 2002-05-31
9
1,763 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
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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Windows 10 Modified 2 41
The blinking Office button 4 38
Sorting multiple rows and columns, and count duplicates in Excel 2013 4 68
Excel 2016 formulas 5 26
Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
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.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

910 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

22 Experts available now in Live!

Get 1:1 Help Now