Solved

Question for Neo_mvps

Posted on 2003-12-07
6
334 Views
Last Modified: 2010-08-05
Hey neo, I have a request that you can probably help out on.
I am writing a book on Outlook and am looking for suggestions on automating Outlook.  It needs to be kept within the Office suite.  Something like creating a task from an Excel spreadsheet entry or emailing a powerpoint slide.  Those are my two suggestions besides the one I already have of creating a contact from a Word doc.  Any other suggestions?  Can you provide the code to accomplish it?
Proper acknowledgements will be given, of course.  You may even be able to better handle the one that I have of creating the contact from Word.  I am looking for three.  Thanks.
0
Comment
Question by:slink9
  • 3
  • 3
6 Comments
 
LVL 13

Expert Comment

by:stefri
ID: 9915937
Hi slink9,
What about creating personalized mails from an Excel spreadsheet?
You know the kind of mail an admin may wish to  send his users about checking their personal information in GAL, or the settings of their POP server, or ....
Or doing some mass-mailing to clients about the last version of your product?

Stefri
0
 
LVL 23

Author Comment

by:slink9
ID: 9916588
I am looking for ideas here.  Do you have the macros to do that?  If so, you could email them to me, I will include them, and give you credit for them.  I like the personalized email from a spreadsheet idea.
0
 
LVL 13

Accepted Solution

by:
stefri earned 500 total points
ID: 9935016
Slink9,
Sorry for the delay, away from the keyboard

The following code will send a html mail to a user about his ISP account
Excel sheet is as follow:
Col 1: First name
Col 2: Last name
Col3: Email address
Col 4: ISP account ID
Col 5: Send mail to ( use YES, if you want to send a mail to this user; allow to restrict to such and such user)
Col 6: Sent (updated by the macro)
I am using Redemption software in this macro as it was accessing our GAL
Without Redemption,
Remove Set SafeItem = CreateObject.....
modify SafeItem to theMail

I used to create the HTML body with frontPage
'-------------------------------------------
Option Explicit
Dim olApp As Outlook.Application
Dim theMail As Outlook.MailItem

Function set_body(displayName As String, ISPAccount As String) As String

set_body = "<html>"
set_body = set_body & "<head>"
set_body = set_body & "<meta http-equiv='Content-Type' content='text/html; charset=windows-1252'>"
set_body = set_body & "<meta name='GENERATOR' content='Microsoft FrontPage 4.0'>"
set_body = set_body & "<meta name='ProgId' content='FrontPage.Editor.Document'>"
set_body = set_body & "<title>New Page 1</title>"
set_body = set_body & "</head>"
set_body = set_body & "<body>"
set_body = set_body & "<div><font face='Verdana'>To the attention of: <b>" & displayName & "</b></font><br>"
set_body = set_body & "  <font face='Verdana'> Your"
set_body = set_body & "  ISP account: <font color='#FF0000'><b>(" & ISPAccount & ")</b></font>"
set_body = set_body & "  </font>"
set_body = set_body & "</div>"
set_body = set_body & "<div>"
set_body = set_body & "  &nbsp;"
set_body = set_body & "</div>"
set_body = set_body & "<div>"
set_body = set_body & "  &nbsp;"
set_body = set_body & "</div>"
set_body = set_body & "<div>"
set_body = set_body & "  <font face='Verdana'>According to the"
set_body = set_body & "  ISP billing system you&nbsp;have not used your ISP account"
set_body = set_body & "  since January 1st, 2003.</font>"
set_body = set_body & "</div>"
set_body = set_body & "<div>"
set_body = set_body & "  &nbsp;"
set_body = set_body & "</div>"
set_body = set_body & "<div>"
set_body = set_body & "  <font face='Verdana'><strong>We would like you"
set_body = set_body & "  to confirm by return e-mail that you have a regular use of it</strong>, <strong>otherwise"
set_body = set_body & "  <font color=#ff0000>your ISP account will be deactivated on May 31st, 2003</font></strong>&nbsp;as we"
set_body = set_body & "  cannot afford to keep&nbsp;wasting money on unused subscriptions.</font>"
set_body = set_body & "</div>"
set_body = set_body & "<div>"

set_body = set_body & "  &nbsp;"
set_body = set_body & "</div>"
set_body = set_body & "<div>"
set_body = set_body & "  &nbsp;"
set_body = set_body & "</div>"
set_body = set_body & "<div>"
set_body = set_body & "  &nbsp;"
set_body = set_body & "</div>"
set_body = set_body & "<div>"
set_body = set_body & "  <font face='Verdana' size='2'>Note: <u>Alternative"
set_body = set_body & "  solutions to accessing mail and Intranet</u><font color='#0000ff'></font></font>"
set_body = set_body & "</div>"
set_body = set_body & "<div>"
set_body = set_body & "  &nbsp;"
set_body = set_body & "</div>"
set_body = set_body & "<div>"
set_body = set_body & "  <font face='Verdana' size='2' color='#0000ff'><u>Using"
set_body = set_body & "  CGG laptop: </u>&nbsp;</font>"
set_body = set_body & "</div>"
set_body = set_body & "<div>"
set_body = set_body & "  <ul>"
set_body = set_body & "    <li><font face='Verdana' size='2'>Most"
set_body = set_body & "      of the processing centers and agencies are now connected to our"
set_body = set_body & "      network thus allowing you to access your mailbox through either Outlook or"
set_body = set_body & "      Webmail (requires a specific authorization).</font></li>"
set_body = set_body & "  </ul>"
set_body = set_body & "</div>"
set_body = set_body & "<div>"
set_body = set_body & "  <ul>"
set_body = set_body & "    <li><font face='Verdana' size='2'>A fairly"
set_body = set_body & "      large number of you already have a DSL or a cable internet access at home"
set_body = set_body & "      on which SecureRemote (VPN) can be used to access our internal network"
set_body = set_body & "      (requires a SecurID card)</font></li>"
set_body = set_body & "  </ul>"
set_body = set_body & "</div>"
set_body = set_body & "<div>"
set_body = set_body & "  <ul>"
set_body = set_body & "    <li><font face='Verdana' size='2'>Some"
set_body = set_body & "      countries such as France have free internet access (freesurf, free, ...),"
set_body = set_body & "      on which SecureRemote (VPN) can be used to access our internal network"
set_body = set_body & "      (requires a SecurID card)</font></li>"
set_body = set_body & "  </ul>"
set_body = set_body & "</div>"
set_body = set_body & "<div>"
set_body = set_body & "  <ul>"
set_body = set_body & "    <li><font face='Verdana' size='2'>Some"
set_body = set_body & "      countries such as MiddleEast have only a local ISP offering (i.e."
set_body = set_body & "      ISP is not working) on which SecureRemote (VPN) can be used to"
set_body = set_body & "      access our internal network (requires a SecurID card)</font></li>"
set_body = set_body & "  </ul>"
set_body = set_body & "</div>"
set_body = set_body & "<div>"
set_body = set_body & "  <ul>"
set_body = set_body & "    <li><font face='Verdana' size='2'>Some"
set_body = set_body & "      countries such as South America are better served by ATT than by"
set_body = set_body & "      ISP (some of you have already an ATT account)</font></li>"
set_body = set_body & "  </ul>"
set_body = set_body & "</div>"
set_body = set_body & "<div>"
set_body = set_body & "  <font face='Verdana' color='#0000ff' size='2'><u>Without"
set_body = set_body & "  CGG PC:</u></font>"
set_body = set_body & "</div>"
set_body = set_body & "<div>"
set_body = set_body & "  <font face='Verdana' size='2'>And finally, we"
set_body = set_body & "  are in the process of opening the access to a Webmail and intranet access"
set_body = set_body & "  from any internet access (cybercaf's, hotels, hot-spots, ...), you will only"
set_body = set_body & "  need to carry your SecuriD card and remember your email aliasname and"
set_body = set_body & "  domain.</font>"
set_body = set_body & "</div>"
set_body = set_body & "<div>"
set_body = set_body & "  &nbsp;"
set_body = set_body & "</div>"
set_body = set_body & "<div>"
set_body = set_body & "  &nbsp;"
set_body = set_body & "</div>"
set_body = set_body & "<div>"
set_body = set_body & "  <font face='Verdana'>Regards,</font>"
set_body = set_body & "</div>"
set_body = set_body & "<div>"
set_body = set_body & "  &nbsp;"
set_body = set_body & "</div>"
set_body = set_body & "<div>"
set_body = set_body & "  <font face='Verdana' size='2'>"
set_body = set_body & "  <div>"
set_body = set_body & "    <font face='Verdana' color='#000080' size='2'><strong>IT Security</strong></font>"
set_body = set_body & "  </div>"
set_body = set_body & "  </font>"
set_body = set_body & "</div>"
set_body = set_body & "</body>"
set_body = set_body & "</html>"


End Function
'-----------------------------------------

Sub sendMail_click()
Dim SafeItem As Object

Dim i As Integer
Dim nr As Integer
Dim accISP As String
Dim dispN As String
Dim olExist As Boolean
Dim emailN As String


Dim olApp As Outlook.Application

    olExist = True
    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")

    If Err.Number <> 0 Then
        olExist = False
        Err.Clear
        On Error Resume Next
        If Not IsEmpty(olApp) Then
            Set olApp = CreateObject("Outlook.Application")
        End If
       
        If Err.Number <> 0 Then
            Err.Clear
            MsgBox "Cant start Outlook", vbCritical, "Error"
            Exit Sub
        End If
    End If
    'olApp.Visible = True
    nr = ActiveSheet.UsedRange.Rows.Count
    For i = 2 To nr
        If Cells(i, 5).Value = "YES" And Cells(i, 6).Value = "" Then
            dispN = Cells(i, 2).Value & ", " & Cells(i, 1).Value
            accISP = Cells(i, 4).Value
            ' Our GAL uses displayname scheme as LastName, FirstName

            If Cells(i, 3).Value = "" Then
                emailN = Cells(i, 2).Value & ", " & Cells(i, 1)
            Else
                emailN = Cells(i, 3).Value
            End If
            Set SafeItem = CreateObject("Redemption.SafeMailItem") 'Create an instance of Redemption.SafeMailItem
            Set theMail = olApp.CreateItem(olMailItem) 'Create a new message
            SafeItem.Item = theMail 'set Item property
            SafeItem.Recipients.Add dispN
            SafeItem.Recipients.ResolveAll
            SafeItem.HTMLBody = set_body(dispN, accISP)
            SafeItem.Subject = "Your ISP account (" & accISP & ")"
            If SafeItem.Recipients.ResolveAll Then
                SafeItem.Send
                Cells(i, 6).Value = "Sent"
            Else
                SafeItem.Display True
                Cells(i, 6).Value = "Manually checked"
            End If

            Set theMail = Nothing
            Set SafeItem = Nothing
           
        End If
    Next

    If olExist = False Then
        olApp.Application.Quit
        Set olApp = Nothing
    End If
End Sub
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 23

Author Comment

by:slink9
ID: 9935331
Thanks.  I have a second example written for mail merging through Word.  I was looking at emailing a PPT slide as the third automation example.  This looks better and more informative.  If you will email the name you want the credit to go to along with a little bio info I will include those as credits for this (address@hotmail.com).
I will try to work with this today or tomorrow and accept it when I see it working.  Thanks.
0
 
LVL 13

Expert Comment

by:stefri
ID: 9935605
From office, I will try to send you other examples using Excel, Outlook forms and Public folders if you think that your readers couls also be Admins.

Stefri
PS: check my bio on EE
0
 
LVL 23

Author Comment

by:slink9
ID: 9935670
That should be it.  I like a round number of three.  If you have a suggestion better than the Cnotact from Word example I am willing to listen.
The mail merging example is a good one.  It should stay.  Actually I believe the contact created from Word is pretty lame so I would like to replace it with something better.  Thanks again.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Resolve DNS query failed errors for Exchange
If you don't know how to downgrade, my instructions below should be helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now