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 have this problem getting email to work.
The VBA program given just does not cut it in various ways:
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.
#If VBA Then ...#Else...#End IfTypo - should read #If VBA7
On 27/12/2021 07:26, Robert Baer wrote:* word wrapping is courtesy of the NG, so nothing needs to be fixed WRT
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* adding "Option Explicit" changed nothing; same exact problems.
Option Explicit, fix the red lines, then do Debug/Compile to flag other lines.
* Nope; "Only comments may appear after End Sub, End Function, or 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
* Have NO control as to where the code goes..winds up in Sheet 1Problem: 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.
* Well, the code (if it could work) sez to look at that DLL.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
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/
As I indicated, the If is stupid and is not needed; the plain Private#If VBA Then ...#Else...#End IfTypo - should read #If VBA7
PT
On 27/12/2021 07:26, Robert Baer wrote:* Have ZERO control, that is where the code was placed and I have no way
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.* How?
https://www.excelcampus.com/vba/code-modules-event-procedures/
On 27/12/2021 07:26, Robert Baer wrote:* Have no control WRT code placement.
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.* Is there a hidden trick to move/place code there?
https://www.excelcampus.com/vba/code-modules-event-procedures/
On 27/12/2021 07:26, Robert Baer wrote:* Have no way to access module level, unless there a hidden trick.
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
On 27/12/2021 07:26, Robert Baer wrote:* word wrapping is courtesy of the/my NG environment.
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.
* Note the "only comments" bit.
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 "declare" is not recognized as a keyword.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
The compiler hates comments.#If VBA Then ...#Else...#End IfTypo - should read #If VBA7
PT
Well, Excel gives me NO choice as to where the heck the macro is placed; it ALWAYS in the Sheet section.
Use a code module instead.
https://www.excelcampus.com/vba/code-modules-event-procedures/
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?
On 28/12/2021 05:38, Robert Baer wrote:
Well, Excel gives me NO choice as to where the heck the macro is
Use a code module instead.
https://www.excelcampus.com/vba/code-modules-event-procedures/
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.
On 27/12/2021 07:26, Robert Baer wrote:I was using MS Office 2000 Premium; I installed a more modern MS
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 wrote:
On 27/12/2021 07:26, Robert Baer wrote:
* Have no way to access module level, unless there a hidden trick.
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.
On 28/12/2021 06:18, Robert Baer wrote:
Peter T wrote:
On 27/12/2021 07:26, Robert Baer wrote:
* Have no way to access module level, unless there a hidden trick.
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.
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
On 28/12/2021 06:18, Robert Baer wrote:
Peter T wrote:
On 27/12/2021 07:26, Robert Baer wrote:
* Have no way to access module level, unless there a hidden trick.
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.
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
Peter T wrote:
On 28/12/2021 06:18, Robert Baer wrote:
Peter T wrote:
On 27/12/2021 07:26, Robert Baer wrote:
* Have no way to access module level, unless there a hidden trick.
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.
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
Peter T wrote:
On 28/12/2021 06:18, Robert Baer wrote:Code as given will be found at: http://www.oil4lessllc.org/Excel/
Peter T wrote:
On 27/12/2021 07:26, Robert Baer wrote:
* Have no way to access module level, unless there a hidden trick.
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.
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
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.
On 04/01/2022 01:08, Robert Baer wrote:Had some other computer problems had to fix, but got the XLS
Peter T wrote:
On 28/12/2021 06:18, Robert Baer wrote:Code as given will be found at: http://www.oil4lessllc.org/Excel/
Peter T wrote:
On 27/12/2021 07:26, Robert Baer wrote:
* Have no way to access module level, unless there a hidden trick.
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.
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
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.
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
Peter T wrote:
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
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
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)?In Excel's VBE, Tools / References, scroll down and tick 'Microsoft
On 16/01/2022 07:26, Robert Baer wrote:* What I said. Moved the procedure/function code to the module section,
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.
* Look now.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'.
* Does not exist.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* All I can say, is nope; I am dead in the water.
is, not necessarily Outlook.
Peter T
| Sysop: | Keyop |
|---|---|
| Location: | Huddersfield, West Yorkshire, UK |
| Users: | 296 |
| Nodes: | 16 (2 / 14) |
| Uptime: | 84:16:11 |
| Calls: | 6,658 |
| Calls today: | 4 |
| Files: | 12,203 |
| Messages: | 5,333,601 |
| Posted today: | 1 |