Back to jkp-ads.com |
Ron de Bruin
|
Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.
Luckily, Ron was kind enough to allow me to publish all of his Excel content here.
Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.
Kind regards
Jan Karel Pieterse
Important read this :
The code on this page is only working when you use Outlook as your mail
program.
Copy the code in a Standard module of your workbook, if you just
started with VBA see this page.
Where do I paste
the code that I find on the internet
Check out this Tip page for changing the code on this page.
Tips for changing the code examples
To run a
macro automatic when you manual change a specific cell you can use the
Change event in a worksheet module.The example on this
page use Cell A1 and will run the macro if the cell value >200.
1) Right click on a sheet tab and choose view code
2) Paste the event below in the sheet module.
3)
Alt-q to go back to Excel
Note: Change
YourMacroName to the name of your macro in the code.
If you want the code
to work for another cell or more cells you can change the range in the
event.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Application.Intersect(Range("A1"), Target) Is Nothing Then If IsNumeric(Target.Value) And Target.Value > 200 Then Call YourMacroName End If End If End Sub
Example mail macro
Test this example macro to
create/display a Outlook mail with a small text message.
You must copy
this macro in a standard module and not in the worksheet module, see this
page how.
Note: I use
.Display in the code to display the mail, you can change that to .Send
Do not forget to change Call YourMacroName to
Call Mail_small_Text_Outlook in the Change event.
Sub Mail_small_Text_Outlook() 'For Tips see: https://jkp-ads.com/rdb/win/winmail/Outlook/tips.htm 'Working in Excel 2000-2016 Dim OutApp As Object Dim OutMail As Object Dim strbody As String Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) strbody = "Hi there" & vbNewLine & vbNewLine & _ "Cell A1 is changed" & vbNewLine & _ "This is line 2" & vbNewLine & _ "This is line 3" & vbNewLine & _ "This is line 4" On Error Resume Next With OutMail .To = "ron@debruin.nl" .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = strbody 'You can add a file like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Send End With On Error GoTo 0 Set OutMail = Nothing Set OutApp = Nothing End Sub
This is not so easy and can be tricky. You can
download a example workbook here with
two examples that both use the
Worksheet_Calculate event :
1: check the value of
one formula cell
2: check the value of more then one
formula cell
In the examples we create a Outlook mail if the value of
the formula(s) >200
Download Example
workbook
If you want to use the Intellisense help showing you the properties and
methods of the objects as you type you can use Early Binding.
Bit faster also when you run your code but you can have problems when you
distribute your workbooks. Excel will automatic update the reference number
to Outlook when you open your workbook in a higher version of Excel/Outlook
but not update it when you open it in a lower version of Excel/Outlook. With
Late Binding as I used in the macro examples you not have
this problem.
Add a reference to the Microsoft Outlook Library in
Excel
1) Go to the VBA editor with the shortcut
Alt - F11
2) Click on
Tools>References in the Menu bar
3) Place a
Checkmark before Microsoft Outlook ? Object Library
Where ? is the Outlook version number
Then replace
this three lines in the code
Dim OutApp As
Object
Dim OutMail As Object
Set OutMail = OutApp.CreateItem(0)
With this three lines
Dim OutApp As
Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutMail =
OutApp.CreateItem(olMailItem)