Solved

Question for Neo_mvps

Posted on 2003-12-07
6
341 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
[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
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

MS Outlook is a world-class email client application that is mainly used for e-communication globally.  In this article, we will discuss the basic idea about MS Outlook, its advanced features, and types of MS Outlook File formats.
Large Outlook files lead to various unwanted errors and corruption issues. Furthermore, large outlook files can also make Outlook take longer to start-up, search, navigate, and shut-down. So, In this article, i will discuss a method to make your Out…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…

688 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