[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2002-05-31
9
Medium Priority
?
2,076 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
Technology Partners: 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 400 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
I came across an unsolved Outlook issue and here is my solution.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

830 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