Results 1 to 5 of 5

Thread: Excel/Outlook VBA

  1. #1
    Join Date
    Feb 2011
    Posts
    27

    Excel/Outlook VBA

    I work in the human resources department and was thinking of a small VBA macro to help me automate an otherwise tedious task. Sent emails to all employees on their anniversary. I found an Outlook macro that does exactly this, but this is only possible for contacts in Outlook.
    I'm looking for something to send emails based on an Excel list.

  2. #2
    Join Date
    Feb 2011
    Posts
    27

    Re: Excel/Outlook VBA

    Code:
    Public Sub AnniversaryMail()
        Dim objAppl As Outlook.Application
        Dim objNS As Outlook.NameSpace
        Dim objContact As Outlook.ContactItem
        Dim objNewmail As MailItem
        Dim objDrafts As MAPIFolder
        Dim objVorlage As MailItem
        On Error Resume Next
        Set objAppl = CreateObject("Outlook.Application")
        Set objNS = objAppl.GetNamespace("MAPI")
        Set objDrafts = objNS.GetDefaultFolder(olFolderDrafts)
    
        For Each objContact In objNS.GetDefaultFolder(olFolderContacts).Items.Restrict("[MessageClass] = 'IPM.Contact'")
    
    
            If (Day(objContact.Birthday) = Day(Date)) And (Month(objContact. Anniversary) = Month(Date)) Then
                
                Set objNewmail = Application.CreateItem(olMailItem)
    
    
                With objNewmail
                   
                    .Subject = "Happy Anniversary!"
                    .Recipients.Add objContact.Email1DisplayName
                    .Body = "Dear " & objContact.FirstName & ","
                                    
                    Set objVorlage = objDrafts.Items.Find("[Subject] = ""Happy Anniversary!""")
    
    
                    If Not objVorlage Is Nothing Then
                        .Body = .Body & objVorlage.Body
                        Else
                    
                   .Body = "Happy Anniversary!"
                   
                    End If
                    '.Display
                    .Send
                End With
            End If
        Next objContact
    End Sub
    This is the code that I discussed in previous post. Check b'days in the contacts folders and sent emails to all those who have a Anniversary on the basis of a draft in the Drafts folder.

    The question is, Outlook does not support Application.OnTime function, which would fully automate the process.

    So I want to settle for a solution based on Excel.

  3. #3
    Join Date
    May 2009
    Posts
    637

    Re: Excel/Outlook VBA

    Just one question: Why do you need the OnTime function?

    All you have to do is check daily when you start Outlook and all the Anniversary in the month and the day is ewual for the current month and the day is mailed. No time here.
    There are several posts, one I worked on doing something similar to Excel, but I cannot remember the name of the author. I send everything worked and see if I can post the link.

  4. #4
    Join Date
    May 2009
    Posts
    511

    Re: Excel/Outlook VBA

    Okay, but tell me, are you going to have Excel running all the time just for this?
    I mean it could establish that someone on the staff when he or she starts Outlook data is checked and if necessary send an email. There are several entries where email activation based on cell data is done, but this means you need to keep data in Excel, and Outlook up to date. The problem is whether the mail server will automatically email say a script to be sent. I know that our server does not allow this to prevent spam.You'll have to think about how this is going to be initiated and what is / is not permitted in the level of protection.

  5. #5
    Join Date
    May 2009
    Posts
    543

    Re: Excel/Outlook VBA

    Let me give a clearer picture of the stage.

    1) weekends off, so no one would be about sending messages.
    2) The above code has to be manually activated.
    3) System backup is performed during the weekend and was advised to leave the system running.
    4) I thought of Excel because of the role of OnTime.
    5) If the above code can be activated automatically, perhaps. Bat, then the option of Excel can be removed.

Similar Threads

  1. Integrating Outlook with Microsoft Excel through VBA
    By LuniA in forum MS Office Support
    Replies: 2
    Last Post: 21-02-2012, 05:11 PM
  2. Not able to have outlook preview on Excel 2007.
    By Martyna in forum Windows Software
    Replies: 6
    Last Post: 02-07-2011, 10:46 PM
  3. Outlook reminders with excel worksheet
    By Avinash Kaur in forum Windows Software
    Replies: 5
    Last Post: 16-03-2011, 11:40 PM
  4. Excel to Outlook not working in Windows 7
    By Emily123 in forum Operating Systems
    Replies: 2
    Last Post: 10-04-2010, 01:41 AM
  5. Import contacts in Outlook from Excel
    By Dorota in forum Windows Software
    Replies: 5
    Last Post: 10-11-2008, 02:47 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,664,745.16666 seconds with 17 queries