VBA email cannot work

[Robert Baer]

I have this problem getting email to work.
The VBA program given just does not cut it in various ways:
** QUOTE **
#If VBA7 And Win64 Then
Private Declare PtrSafe Function ShellExecute Lib "shell32.dll"
Alias "ShellExecuteA" ( _
ByVal hwnd As LongPtr, ByVal lpOperation As
String, _
ByVal lpFile As String, ByVal lpParameters As
String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As LongPtr
#Else
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
#End If
Sub SendEMail()
'update by Extendoffice 20160506
Dim xEmail As String
Dim xSubj As String
Dim xMsg As String
Dim xURL As String
Dim i As Integer
Dim k As Double
Dim xCell As Range
Dim xRg As Range
Dim xTxt As String
On Error Resume Next
xTxt = ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("Please select the data range:",
"Kutools for Excel", xTxt, , , , , 8)
If xRg Is Nothing Then Exit Sub
If xRg.Columns.Count <> 3 Then
MsgBox " Regional format error, please check", , "Kutools for
Excel"
Exit Sub
End If
For i = 1 To xRg.Rows.Count
' Get the email address
xEmail = xRg.Cells(i, 2)
' Message subject
xSubj = "Your Registration Code"
' Compose the message
xMsg = ""
xMsg = xMsg & "Dear " & xRg.Cells(i, 1) & "," & vbCrLf & vbCrLf
xMsg = xMsg & " This is your Registration Code "
xMsg = xMsg & xRg.Cells(i, 3).Text & "." & vbCrLf & vbCrLf
xMsg = xMsg & " please try it, and glad to get your feedback! "
& vbCrLf
xMsg = xMsg & "Skyyang"
' Replace spaces with %20 (hex)
xSubj = Application.WorksheetFunction.Substitute(xSubj, " ", "%20")
xMsg = Application.WorksheetFunction.Substitute(xMsg, " ", "%20")
' Replace carriage returns with %0D%0A (hex)
xMsg = Application.WorksheetFunction.Substitute(xMsg, vbCrLf,
"%0D%0A")
' Create the URL
xURL = "mailto:" & xEmail & "?subject=" & xSubj & "&body=" & xMsg
' Execute the URL (start the email client)
ShellExecute 0&, vbNullString, xURL, vbNullString,
vbNullString, vbNormalFocus
' Wait two seconds before sending keystrokes
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%s"
Next
End Sub
** END QUOTE **

Stupidity: one knows the environment, so the #If..#End If is not needed.

Problem: the "Private Declare Function ShellExecute Lib "shell32.dll"
Alias "ShellExecuteA" () produces the error message "Only comments may
appear after End Sub, End Function, or End Property"

So I moved the #If..#End If to after the End Sub; no joy.

Turns out that no matter what one does with this Declare Function
statement, the compiler barfs.
Removing it means the ShellExecute call in the program body cannot
execute (compiler complains the function is not declared).

So the code as-is is useless.

How in the heck can one send an e-mail in Excel VBA?

Thanks.

--
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

[Adrian Caspersz]

On 27/12/2021 07:26, Robert Baer wrote:

>
>   Turns out that no matter what one does with this Declare Function
> statement, the compiler barfs.
>   Removing it means the ShellExecute call in the program body cannot
> execute (compiler complains the function is not declared).

I think you may be trying to run this from a Sheet Module?

Use a code module instead.

https://www.excelcampus.com/vba/code-modules-event-procedures/


--
Adrian C

[Peter T]

On 27/12/2021 07:26, Robert Baer wrote:

>   I have this problem getting email to work.
>   The VBA program given just does not cut it in various ways:

Well it works fine for me! That said as posted it's heavily word-wrapped
which needed fixing, including a couple of lines. Head the module with
Option Explicit, fix the red lines, then do Debug/Compile to flag other
lines.

>
>   Stupidity: one knows the environment, so the #If..#End If is not needed.

Probably not with only this particular API, the PtrSafe declaration will
work in all versions from 2010, 32 or 64 bit. But to cater for any older
versions use:

#If VBA Then ...#Else...#End If

>   Problem: the "Private Declare Function ShellExecute Lib "shell32.dll"
> Alias "ShellExecuteA" () produces the error message "Only comments may
> appear after End Sub, End Function, or End Property"
>
>   So I moved the #If..#End If to after the End Sub; no joy.

No don't do that, the APIs must be declared at module level.

>   Turns out that no matter what one does with this Declare Function
> statement, the compiler barfs.

As I mentioned it works fine for me so I suspect for you just the
wrapping needs sorting out. That is, assuming the system has a default
mail-app for ShellExecute to find.

Peter T

[Peter T]

> #If VBA Then ...#Else...#End If

Typo - should read #If VBA7

PT

[Robert Baer]

Well, Excel gives me NO choice as to where the heck the macro is
placed; it ALWAYS in the Sheet section.

In my case, I started with a spreadsheet, then used Tools, Macro,
Macros, Create (or Edit when making changes).
It is in Sheet 1 (CONDUP) and I have no clue as to why/where that
name "condup" came from.

So, if the only place the code will work, I will have to use magic
to move it into the Modules section as a new Module1.

I did some fiddling, and found no such magic.

Suggestions?
Thanks

[Robert Baer]

Peter T wrote:
> On 27/12/2021 07:26, Robert Baer wrote:
>>    I have this problem getting email to work.
>>    The VBA program given just does not cut it in various ways:
>
> Well it works fine for me! That said as posted it's heavily word-wrapped

* word wrapping is courtesy of the NG, so nothing needs to be fixed WRT
lines.

> which needed fixing, including a couple of lines. Head the module with
> Option Explicit, fix the red lines, then do Debug/Compile to flag other
> lines.

* adding "Option Explicit" changed nothing; same exact problems.

>>
>>    Stupidity: one knows the environment, so the #If..#End If is not
>> needed.
>
> Probably not with only this particular API, the PtrSafe declaration will
> work in all versions from 2010, 32 or 64 bit. But to cater for any older
> versions use:
>
> #If VBA Then ...#Else...#End If

* Nope; "Only comments may appear after End Sub, End Function, or End

Property"

>
>>    Problem: the "Private Declare Function ShellExecute Lib
>> "shell32.dll" Alias "ShellExecuteA" () produces the error message
>> "Only comments may appear after End Sub, End Function, or End Property"
>>
>>    So I moved the #If..#End If to after the End Sub; no joy.
>
> No don't do that, the APIs must be declared at module level.

* Have NO control as to where the code goes..winds up in Sheet 1
(CONDUP); do not ask where that name came from - no clue.

>
>>    Turns out that no matter what one does with this Declare Function
>> statement, the compiler barfs.
>
> As I mentioned it works fine for me so I suspect for you just the
> wrapping needs sorting out. That is, assuming the system has a default
> mail-app for ShellExecute to find.

* Well, the code (if it could work) sez to look at that DLL.
Also, the word "declare" is not a keyword and removing it does not help.


>
> Peter T

[Robert Baer]

As I indicated, the If is stupid and is not needed; the plain Private
line will do IF was accepted.
BUT "Only comments may appear after End Sub, End Function, or End
Property"

Am code DOA.

[Robert Baer]

Adrian Caspersz wrote:
> On 27/12/2021 07:26, Robert Baer wrote:
>
>>
>>    Turns out that no matter what one does with this Declare Function
>> statement, the compiler barfs.
>>    Removing it means the ShellExecute call in the program body cannot
>> execute (compiler complains the function is not declared).
>
> I think you may be trying to run this from a Sheet Module?

* Have ZERO control, that is where the code was placed and I have no way
of moving or placing it anywhere else.

>
> Use a code module instead.

* How?

>
> https://www.excelcampus.com/vba/code-modules-event-procedures/

[Robert Baer]

Peter T wrote:
> On 27/12/2021 07:26, Robert Baer wrote:
>>    I have this problem getting email to work.
>>    The VBA program given just does not cut it in various ways:
>
> Well it works fine for me! That said as posted it's heavily word-wrapped
> which needed fixing, including a couple of lines. Head the module with
> Option Explicit, fix the red lines, then do Debug/Compile to flag other
> lines.
>>
>>    Stupidity: one knows the environment, so the #If..#End If is not
>> needed.
>
> Probably not with only this particular API, the PtrSafe declaration will
> work in all versions from 2010, 32 or 64 bit. But to cater for any older
> versions use:
>
> #If VBA Then ...#Else...#End If
>
>>    Problem: the "Private Declare Function ShellExecute Lib
>> "shell32.dll" Alias "ShellExecuteA" () produces the error message
>> "Only comments may appear after End Sub, End Function, or End Property"
>>
>>    So I moved the #If..#End If to after the End Sub; no joy.
>
> No don't do that, the APIs must be declared at module level.

* Have no way to access module level, unless there a hidden trick.

>
>>    Turns out that no matter what one does with this Declare Function
>> statement, the compiler barfs.
>
> As I mentioned it works fine for me so I suspect for you just the
> wrapping needs sorting out. That is, assuming the system has a default
> mail-app for ShellExecute to find.
>
> Peter T

[Robert Baer]

Adrian Caspersz wrote:
> On 27/12/2021 07:26, Robert Baer wrote:
>
>>
>>    Turns out that no matter what one does with this Declare Function
>> statement, the compiler barfs.
>>    Removing it means the ShellExecute call in the program body cannot
>> execute (compiler complains the function is not declared).
>
> I think you may be trying to run this from a Sheet Module?

* Have no control WRT code placement.

>
> Use a code module instead.

* Is there a hidden trick to move/place code there?

>
> https://www.excelcampus.com/vba/code-modules-event-procedures/

[Robert Baer]

Peter T wrote:
> On 27/12/2021 07:26, Robert Baer wrote:
>>    I have this problem getting email to work.
>>    The VBA program given just does not cut it in various ways:
>
> Well it works fine for me! That said as posted it's heavily word-wrapped
> which needed fixing, including a couple of lines. Head the module with
> Option Explicit, fix the red lines, then do Debug/Compile to flag other
> lines.

* word wrapping is courtesy of the/my NG environment.
All line lengths are OK and have the proper _ syntax at the physical end.

>>
>>    Stupidity: one knows the environment, so the #If..#End If is not
>> needed.
>
> Probably not with only this particular API, the PtrSafe declaration will
> work in all versions from 2010, 32 or 64 bit. But to cater for any older
> versions use:
>
> #If VBA Then ...#Else...#End If
>
>>    Problem: the "Private Declare Function ShellExecute Lib
>> "shell32.dll" Alias "ShellExecuteA" () produces the error message
>> "Only comments may appear after End Sub, End Function, or End Property"
>>
>>    So I moved the #If..#End If to after the End Sub; no joy.
>
> No don't do that, the APIs must be declared at module level.

* Note the "only comments" bit.

>
>>    Turns out that no matter what one does with this Declare Function
>> statement, the compiler barfs.
>
> As I mentioned it works fine for me so I suspect for you just the
> wrapping needs sorting out. That is, assuming the system has a default
> mail-app for ShellExecute to find.

* Note "declare" is not recognized as a keyword.

>
> Peter T

[Robert Baer]

The compiler hates comments.

[Adrian Caspersz]

On 28/12/2021 05:38, Robert Baer wrote:
>>
>> Use a code module instead.
>>
>> https://www.excelcampus.com/vba/code-modules-event-procedures/
>>
>>
>    Well, Excel gives me NO choice as to where the heck the macro is
> placed; it ALWAYS in the Sheet section.
>
>    In my case, I started with a spreadsheet, then used Tools, Macro,
> Macros, Create (or Edit when making changes).
>    It is in Sheet 1 (CONDUP) and I have no clue as to why/where that
> name "condup" came from.
>
>    So, if the only place the code will work, I will have to use magic
> to move it into the Modules section as a new Module1.
>
>    I did some fiddling, and found no such magic.
>
>    Suggestions?

This looks like you are starting out badly in excel coding and I fear
for exactly what email will eventually be sent on the results of this
endeavour. Spam?

A change of attitude and acceptance that you need to study will help.

There are no shortcuts to this learning, but plenty of tutorials you can
find both on google and youtube.

Sorry.

--
Adrian C

[Robert Baer]

With a little trouble, i found the method to place the code in the
module section.
However, the reality was, to be polite, not so nice; 20 minutes of
fussing.

Net result?
Still does not work.

I find the following words are not recognized by the compiler:
Private, Declare, or Function, making the exercise moot.

Thanks, R. Baer

[Robert Baer]

I was using MS Office 2000 Premium; I installed a more modern MS
Office 2010.
That Excel actually recognizes Private, Declare, and Function which
is a big step forward.
B U T.
The compiler still complains "Only comments may appear after End Sub,
End Function, or End Property".

Gack!

[Peter T]

On 28/12/2021 06:18, Robert Baer wrote:
> Peter T wrote:
>> On 27/12/2021 07:26, Robert Baer wrote:

>>
>>>    Problem: the "Private Declare Function ShellExecute Lib
>>> "shell32.dll" Alias "ShellExecuteA" () produces the error message
>>> "Only comments may appear after End Sub, End Function, or End Property"
>>>
>>>    So I moved the #If..#End If to after the End Sub; no joy.
>>
>> No don't do that, the APIs must be declared at module level.
> * Have no way to access module level, unless there a hidden trick.

All API's should be declared (ie written) at the TOP of a module, in
this case a 'normal' module.

The term "module level" means the TOP of a module where any any code
goes that is not a procedure eg like your Sub SendEMail(). This is where
external functions (APIs) are 'declared', also variables which you want
to retain 'scope' and possibly share with multiple procedures.

As I mentioned your code really does work. If still struggling upload a
workbook with it to a file sharing site and post the link.

Peter T

[Robert Baer]

Code as given will be found at: http://www.oil4lessllc.org/Excel/

Nobody told me where and how to place code, and there are 5 places:
Sheet Modules under Microsoft Excel Objects (the default location), User
form under Forms, Modules under Modules, Class under Class modules,
ThisWorkbook under Microsoft Excel Objects and I hear Code Modules but
under what I do not know.

Also, nobody said the code had different parts: the
procedure/function part which was given inline with the rest of the
given code; then the unstated option part.
Naturally, where each part must be.

And you added another variable TOP; I assume that means the first
module if there is more than one in any of the 5 sections I have listed.

Thanks in advance for the info.
Sincerely, R. Baer

[Robert Baer]

I have another option to send emails: send personalized mass emails
to a list from Excel via Outlook.
So I follow the script:
1. Launch a new blank Word document, and then click Mailings > Select
Recipients > Use an Existing List.
2. In the Select Data Source window, choose the workbook which includes
the data range that you want to use, and click Open button.

Well that is impossible, because there is NO Select Data Source anywhere.

So it seems that there is no way in heck i can send e-mails from a
list, and will be forced to do it one-by-one manually, 2500 times.

Thanks, R. Baer

[Philip Herlihy]

In article <16c6f4b4a8ff960a$1$43267$e2dd...@news.thecubenet.com>,
rober...@localnet.com says...

If you have Outlook application available then that's the way to go. I saw
your original (and creative!) solution was to build a 'mailto:' link and then
try to 'click' it within the VBA - I wouldn't have time to experiment with that
but I was surprised! If you have Outlook installed, you can simply create an
instance of it within your Excel VBA, and that gives you all the options you
could want. This link (which I haven't studied in depth, so look for others if
you hit snags) seems to run through the process, and you can adapt what it
sends to suit your needs.
https://www.automateexcel.com/vba/send-emails-outlook/

--

Phil, London

[Peter T]

On 04/01/2022 01:08, Robert Baer wrote:
> Peter T wrote:
>> On 28/12/2021 06:18, Robert Baer wrote:
>>> Peter T wrote:
>>>> On 27/12/2021 07:26, Robert Baer wrote:
>>
>>>>
>>>>>    Problem: the "Private Declare Function ShellExecute Lib
>>>>> "shell32.dll" Alias "ShellExecuteA" () produces the error message
>>>>> "Only comments may appear after End Sub, End Function, or End
>>>>> Property"
>>>>>
>>>>>    So I moved the #If..#End If to after the End Sub; no joy.
>>>>
>>>> No don't do that, the APIs must be declared at module level.
>>> * Have no way to access module level, unless there a hidden trick.
>>
>> All API's should be declared (ie written) at the TOP of a module, in
>> this case a 'normal' module.
>>
>> The term "module level" means the TOP of a module where any any code
>> goes that is not a procedure eg like your Sub SendEMail(). This is
>> where external functions (APIs) are 'declared', also variables which
>> you want to retain 'scope' and possibly share with multiple procedures.
>>
>> As I mentioned your code really does work. If still struggling upload
>> a workbook with it to a file sharing site and post the link.
>>
>> Peter T
>>
>>
>>
>>
>   Code as given will be found at: http://www.oil4lessllc.org/Excel/

You uploaded the code as a text file, same as you posted here albeit
without the line wrapping.

As before the code works but we need to see how it's implemented in your
workbook. If you can, add it to workbook as you already have it, with
some sample email data on the worksheet, and upload it.

>   Nobody told me where and how to place code, and there are 5 places:
> Sheet Modules under Microsoft Excel Objects (the default location), User
> form under Forms, Modules under Modules, Class under Class modules,
> ThisWorkbook under Microsoft Excel Objects and I hear Code Modules but
> under what I do not know.

As Adrian tried to explain it is incumbent on you to learn all this, if
nobody told you there are plenty of resources. Though it has been
explained in this thread the code needs to go in a 'normal' module, the
type of module that gets added automatically when you record a macro.

>   Also, nobody said the code had different parts: the
> procedure/function part which was given inline with the rest of the
> given code; then the unstated option part.
>   Naturally, where each part must be.

Nobody said...

>   And you added another variable TOP; I assume that means the first
> module if there is more than one in any of the 5 sections I have listed.

Top of the module (any/all modules have a Top) is where 'module level
declarations' go, such APIs and variables which have 'scope' throughout
the module( possibly plural but another subject). Procedures, like
Sub's and Functions, go below.

Peter T

[Robert Baer]

Had some other computer problems had to fix, but got the XLS
file/program uploaded to http://www.oil4lessllc.org/Excel/.

I do not understand what you mean by Top.

Thanks.

[Peter T]

On 09/01/2022 10:40, Robert Baer wrote:

>   Had some other computer problems had to fix, but got the XLS
> file/program uploaded to http://www.oil4lessllc.org/Excel/.
>
>   I do not understand what you mean by Top.
>
>      Thanks.

Imagine holding the module against a wall the normal way up, the 'Top'
of the module is nearest to the ceiling.

The code you posted in your OP was fine, the code in your workbook has
evolved into a mess. The API declarations are 'inside' the Sub
SendEMail(), despite at lengths my trying to explain that's where they
don't go, and where they should go. You commented the correct and full
extent of the ShellExectue API, and replaced it without any of its
arguments, it could never work. So replace the code in your workbook
with that in your OP.

It still won't work properly of because of the line 'xEmail =
xRg.Cells(i, 2)', because cells in the 2nd column are not email
addresses. Change the 2 to 1 to get the addresses in col-A. Be sure to
select cells in colA:colC. Could never have known that without seeing
your actual workbook.

For testing comment the SendKeys line, the email will need to be active
for this to work. On which point SendKeys is notoriously unreliable, as
previously suggested much better to 'automate' Outlook, IOW use
Outlook's VBA code from within Excel.

Peter T

[Robert Baer]

I fixed that email assignment, and moved the procedure code to the
module section.
Results:
1) I cannot find that code is cannot make any changes to it
2) Running the code yields "Sub or Function not defined"

I have access to Outlook 365 and presumably Excel 365 courtesy of
Office 365...
Outlook has VBA code?
What do I look for (and where)?

Thanks,
R. Baer

[Philip Herlihy]

In article <16caaf88968cc2bd$1$4098531$e0dd...@news.thecubenet.com>,
rober...@localnet.com says...

If you have the full Outlook desktop application installed (not just the online
webmail service - I wish they'd rename one or the other!) then you have access
to the Outlook VBA object model. In (desktop)Outlook, key ALT+F11 and the full
editor will appear after a macro security warning (same as it does in Excel).
However, off the top of my head I don't think Office 365 does include full
desktop Outlook.

To use Outlook objects, you'll need to set a reference in your Excel project to
the Outlook Object Library (in the VBA Editor, Tools/References, then scroll
and tick). The Object Browser (under View menu, or tap F2) is where you can
start drilling down into all the functions, methods and properties available to
you - but it's reference rather than tutorial material! So search for a few
examples of code, copy them into the editor, and then look up in the Object
Browser the functions etc which are used. It's baffling initially, but starts
to make sense afer a while. Don't neglect to trap errors, at least so a
message pops up, or you could be guessing for a long time!

--

Phil, London

[Peter T]

On 16/01/2022 07:26, Robert Baer wrote:

>  I fixed that email assignment, and moved the procedure code to the
> module section.
>   Results:
> 1) I cannot find that code is cannot make any changes to it

I don't know what that's supposed to mean.

> 2) Running the code yields "Sub or Function not defined"

I can only reiterate the code in your OP is fundamentally fine. It's
impossible for us to know why it isn't working now so if you want to
upload a new workbook with what you've changed ...

>   I have access to Outlook 365 and presumably Excel 365 courtesy of
> Office 365...
>   Outlook has VBA code?

AFAIK all 365 subscriptions include desktop Outlook and if so would
include its VBA and exposed 'object model'.

>   What do I look for (and where)?

In Excel's VBE, Tools / References, scroll down and tick 'Microsoft
Outlook ##.0 Object Library'. All Outlook's VBA methods will then be
available for you in Excel. But there's more to it and there's learning
curve which you really need to research for yourself.

FWIW your current code should work with whatever your default email app
is, not necessarily Outlook.

Peter T

[Robert Baer]

Peter T wrote:
> On 16/01/2022 07:26, Robert Baer wrote:
>
>>   I fixed that email assignment, and moved the procedure code to the
>> module section.
>>    Results:
>> 1) I cannot find that code is cannot make any changes to it
>
> I don't know what that's supposed to mean.

* What I said. Moved the procedure/function code to the module section,
so it is no longer in the sheet section.
The result is that I can no longer find or see that code.

>
>> 2) Running the code yields "Sub or Function not defined"
>
> I can only reiterate the code in your OP is fundamentally fine. It's
> impossible for us to know why it isn't working now so if you want to
> upload a new workbook with what you've changed ...

* Look now.

>
>>    I have access to Outlook 365 and presumably Excel 365 courtesy of
>> Office 365...
>>    Outlook has VBA code?
>
> AFAIK all 365 subscriptions include desktop Outlook and if so would
> include its VBA and exposed 'object model'.
>

* I looked and found I now only have Outlook 365 with NO mail merge type
function; therefore the only thing I can do is a one at a time mailing;
a gruesome one-at-a-time operation.

>>    What do I look for (and where)?
> In Excel's VBE, Tools / References, scroll down and tick 'Microsoft
> Outlook ##.0 Object Library'. All Outlook's VBA methods will then be
> available for you in Excel. But there's more to it and there's learning
> curve which you really need to research for yourself.

* Does not exist.

>
> FWIW your current code should work with whatever your default email app
> is, not necessarily Outlook.

* All I can say, is nope; I am dead in the water.

>
> Peter T

Thanks.