XPost: microsoft.public.excel, microsoft.public.excel.programming

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

Typo - should read #If VBA7

PT

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

XPost: microsoft.public.excel, microsoft.public.excel.programming

Peter T wrote:

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

 Typo - should read #If VBA7

PT

The compiler hates comments.


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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

XPost: microsoft.public.excel, microsoft.public.excel.programming

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

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

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

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

XPost: microsoft.public.excel, microsoft.public.excel.programming

Peter T wrote:

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

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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)