Solved

Outlook Macro to compose an email

Posted on 2009-07-01
11
664 Views
Last Modified: 2012-06-27
Can some please help with an outlook macro that does:
1.  Constantly monitor the folder C:\My Documents\.
2.  If it sees a new file called "Subject", take whatever is in the clipboard as the Title of the email.  And delete the folder "Subject".
3.  If it sees a new file called "Body", take whatever is in the clipboard as the body of the email. and delete the file "Body".
4.  Send email to xyz@gmail.com
5.  Loop steps 1-4 until I exit Outlook.

The reason I need this macro is that I have a script that is going to generate the subject and the Body of the email, and I like to use Outlook to send out the email.

Really appreciate if someone can help me out.
0
Comment
Question by:Folsomite
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
11 Comments
 
LVL 12

Expert Comment

by:kevin_u
ID: 24758638
why not use:

shell execute on:

mailto:xyz@gmail.com?subject=whatever&body=whatever body text&

Just a thought.
0
 

Author Comment

by:Folsomite
ID: 24758707
No, it'll not work for me.  I cannot invoke the above command from activeperl script I am using to generate the content of the email.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24758791
Hi,  Folsomite.

Your plan is to use the files as event triggers, right?  If so, then I see the potential for synchronization problems unless your PERL script will never write out more than one set of files at a time.  Could your script write the subject and body into a single file?  
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:Folsomite
ID: 24758909
Hello BlueDevilFan,
Yes, you are right, I am using the files as event triggers.  My perl script will never write more than one file at a time.  
1.  The perl script copies the subject matter to the windows clipboard, wait for the outlook macro to get the content of the clipboard and delete the "Subject" folder. When the folder "subject" is deleted by the macro, it's a signal to perl script that Outlook has retrieved the Subject from the clipboard.
2.  The perl script copies the body matter to the windows clipboard, wait for outlook macro to get the content of the clipboard and delete the folder "Body".

if it's easier to write a macro that deals with one file, I can refine my perl script to write the subject and the body into a single file.  Let me know how you like to identify the subject and the body from the single file, I will rewrite the perl script to generate that format.

Thanks
Folsomite
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24758991
Ok.  One more question.  What version of Outlook are you using?
0
 

Author Comment

by:Folsomite
ID: 24759045
I have Outlook 2002 on one computer and outlook 2003 on another.  If a single macro doesn't work for these versions of outlook, please write me the macro for outlook 2002.

Thanks very much.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24759072
A single macro should work for both.  There's an issue you need to know about up front.  Outlook versions 2000 - 2003 include security features that prohibit sending messages from code without the user's knowledge.  Each message is going to display a dialog-box warning that an application is accessing your mailbox and asking for you to allow it to continue.  You have to click a button to allow the code to proceed.  This kills any kind of fully automated process.  Outlook security cannot be turned off, but there are ways to work around it.  

1.  Sign the code.  Here's a link to instructions on doing that: http://msdn.microsoft.com/en-us/library/aa155754(office.10).aspx
2.  Use ClickYes (http://www.contextmagic.com/express-clickyes/), a small utility that'll click the Yes button for you.  It creates a security hole though, since a virus could start sending messages and ClickYes would click the Yes button for it too.  
3.  Use Redemption (http://www.dimastr.com), a COM library that enables code to safely bypass Outlook security.

If this isn't a show-stopper, then I'll go ahead and work on getting the code together.
0
 

Author Comment

by:Folsomite
ID: 24759285
BlueDevilFan,
Thanks for the heads up.  solution #1 you pointed out will work for me, I can lower the Macro security, but since I have McAfee anti-virus installed, I should be OK.

Thanks again.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24819920
Apologies for being so slow.  I hope to get to this tomorrow.
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 24831288
Ok, here's my take on a solution.  Follow these instructions to use it.

1.  Start Outlook
2.  Click Tools > Macro > Visual Basic Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects
4.  If not already expanded, expand Modules
5.  Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert > Module.
6.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
7.  Edit the code as needed.  I included comments wherever something needs to or can change
8.  Click the diskette icon on the toolbar to save the changes
9.  Close the VB Editor

Notes

1.  Run the macro BeginMonitoring to start the process.
2.  Be sure to run EndMonitoring before exiting Outlook.  Failure to do so will cause Outlook to crash.
3.  This solution assumes that the monitored folder will only contain the triggering files named Subject and Body.
4.  This solution assumes that the files will always be created in sequence, with Subject coming first followed by Body.
Option Explicit
 
Declare Function SetTimer Lib "User32" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerfunc As Long) As Long
Declare Function KillTimer Lib "User32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
Declare Function CloseClipboard Lib "User32" () As Long
Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) As Long
Declare Function EmptyClipboard Lib "User32" () As Long
Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long
Declare Function SetClipboardData Lib "User32" (ByVal wFormat As Long, ByVal hMem As Long) As Long
Declare Function GetClipboardData Lib "User32" (ByVal wFormat As Long) As Long
 
Public Const GHND = &H42
Public Const CF_TEXT = 1
Public Const MAXSIZE = 4096
 
'Change the path on the next line to that of the folder you want to monitor'
Const FOLDER_NAME = "C:\eeTesting\FolderMon"
'Change the interval on the next line.'
Const INTERVAL_SECONDS = 5
Const MACRO_NAME = "Monitor Folder"
 
Private objFSO As Object
Private objFolder As Object
Private objFile As Object
Private lngTimerID As Long
Private strFolderName As String
Private olkMessage As Outlook.MailItem
 
Private Sub BeginMonitoring()
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    If objFSO.FolderExists(FOLDER_NAME) Then
        Set objFolder = objFSO.GetFolder(FOLDER_NAME)
        If INTERVAL_SECONDS > 0 Then
            ActivateTimer INTERVAL_SECONDS
        Else
            MsgBox "The checking interval must be greater then 0 seconds.", vbCritical + vbOKOnly, MACRO_NAME
        End If
    Else
        MsgBox "The folder " & FOLDER_NAME & " does not exist.", vbCritical + vbOKOnly, MACRO_NAME
    End If
End Sub
 
Private Sub EndMonitoring()
    DeactivateTimer
    Set objFSO = Nothing
    Set objFolder = Nothing
    Set objFile = Nothing
End Sub
 
Private Sub TriggerTimer(ByVal hwnd As Long, ByVal uMsg As Long, ByVal idevent As Long, ByVal Systime As Long)
    Debug.Print Time
    If objFolder.Files.count() > 0 Then
        For Each objFile In objFolder.Files
            Select Case objFile.Name
                Case "Subject"
                    Set olkMessage = Application.CreateItem(olMailItem)
                    olkMessage.subject = ClipBoard_GetData()
                    Kill objFile.Path
                Case "Body"
                    With olkMessage
                        .Body = ClipBoard_GetData()
                        .Recipients.Add "xyz@gmail.com"
                        .Send
                    End With
                    Kill objFile.Path
                    Set olkMessage = Nothing
            End Select
        Next
    End If
End Sub
 
Private Sub ActivateTimer(ByVal lngSeconds As Long)
    lngSeconds = lngSeconds * 1000
    'Check to see if timer is running before call to SetTimer
    If lngTimerID <> 0 Then Call DeactivateTimer
    lngTimerID = SetTimer(0, 0, lngSeconds, AddressOf TriggerTimer)
    If lngTimerID = 0 Then
        MsgBox "The timer failed to activate."
    End If
End Sub
 
Private Sub DeactivateTimer()
    Dim lSuccess As Long
    lSuccess = KillTimer(0, lngTimerID)
    If lSuccess = 0 Then
        MsgBox "The timer failed to deactivate."
    Else
        lngTimerID = 0
    End If
End Sub
 
'This code is not mine.  I picked it up somewhere on the Internet.  All credit goes to the original author.'
Function ClipBoard_GetData() As String
   Dim hClipMemory As Long
   Dim lpClipMemory As Long
   Dim MyString As String
   Dim RetVal As Long
 
   If OpenClipboard(0&) = 0 Then
      MsgBox "Cannot open Clipboard. Another app. may have it open"
      Exit Function
   End If
         
   ' Obtain the handle to the global memory
   ' block that is referencing the text.
   hClipMemory = GetClipboardData(CF_TEXT)
   If IsNull(hClipMemory) Then
      MsgBox "Could not allocate memory"
      GoTo OutOfHere
   End If
 
   ' Lock Clipboard memory so we can reference
   ' the actual data string.
   lpClipMemory = GlobalLock(hClipMemory)
 
   If Not IsNull(lpClipMemory) Then
      MyString = Space$(MAXSIZE)
      RetVal = lstrcpy(MyString, lpClipMemory)
      RetVal = GlobalUnlock(hClipMemory)
      
      ' Peel off the null terminating character.
      MyString = Mid(MyString, 1, InStr(1, MyString, Chr$(0), 0) - 1)
   Else
      MsgBox "Could not lock memory to copy string from."
   End If
 
OutOfHere:
 
   RetVal = CloseClipboard()
   ClipBoard_GetData = MyString
 
End Function

Open in new window

0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sqlplus write // 1 14
Simple IF statement in Word 4 53
Outlook emails via Yahoo bizmail - can send but not recieve 6 46
EMAIL RANGES BASED ON CURRENT TIME 12 41
Are you unable to connect or configure Hotmail email account in Microsoft Outlook 2010, 2007? Or Outlook.com emails are not downloading to Outlook? Lets’ see the problem and resolve Outlook Connector error syncing folder hierarchy (0x8004102A).
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

710 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