+ Reply to Thread
Results 1 to 2 of 2

How to copy mails to Excel

  1. #1
    Saravanan
    Guest

    How to copy mails to Excel

    Hi, I will be sending queires through outlook and i will get the replies for
    the queries from the same day /week / month. I want to automate these query
    mails and their respective replies in Ms-Excel. To be precise Mails send and
    receipts in Outlook with its contents has to be updated in Ms-Excel

  2. #2
    Mel Arquiza
    Guest

    RE: How to copy mails to Excel

    Hi Saravanan,

    Cut and paste this code to excel module and run see if this helps.

    Sub EMailActiveWorksheet()


    Dim OL As Object ' Outlook instantiation
    Dim EmailItem As Object ' A new mail item (e-mail)
    Dim FileName As String ' The name of the file we are attaching
    Dim lngLoop As Long ' counter for the FileName length
    Dim TempChar As String ' used for the loop to test for unusable characters
    Dim SaveName As String ' Attachment's new name, after cleaning

    Application.ScreenUpdating = False ' speed up Excel processing time
    Set OL = CreateObject("Outlook.Application") ' New Outlook application
    Set EmailItem = OL.CreateItem(olMailItem) ' new MailItem
    FileName = ActiveSheet.Name & " - " & ActiveWorkbook.Name ' create a
    filename on the fly
    For lngLoop = 1 To Len(FileName) ' error check for unusable chars in the
    filename
    TempChar = FileName
    Select Case TempChar
    Case Is = "/", "\", "*", "?", """", "<", ">", "/"
    Case Else
    SaveName = TempChar
    End Select
    Next lngLoop
    ActiveSheet.Cells.Copy ' copy the contents of the ActiveSheet
    Workbooks.Add ' create a new workbook
    Selection.PasteSpecial Paste:=xlValues ' paste the worksheet values into
    the new book
    Selection.PasteSpecial Paste:=xlFormats ' and their formats
    ActiveWorkbook.SaveAs "C:\" & SaveName ' temp file attachment location
    ActiveWorkbook.ChangeFileAccess xlReadOnly ' make access read only
    With EmailItem ' with the newly created e-mail
    .Subject = ActiveWorkbook.Name
    .Body = "This is an example of a single worksheet sent by VBA mail"
    .To = "Enter your Recipients here - change"
    .Importance = olImportanceNormal 'Or olImportanceHigh Or
    olImportanceLow
    .Attachments.Add "C:\" & SaveName
    .Display
    End With
    Kill "C:\" & SaveName ' delete the temporary attachment
    ActiveWorkbook.Close False ' close down the workbook without saving
    (single sheet)

    Application.ScreenUpdating = True ' always remember to switch it back on!!

    Set OL = Nothing ' clean down memory
    Set EmailItem = Nothing

    End Sub

    "Saravanan" wrote:

    > Hi, I will be sending queires through outlook and i will get the replies for
    > the queries from the same day /week / month. I want to automate these query
    > mails and their respective replies in Ms-Excel. To be precise Mails send and
    > receipts in Outlook with its contents has to be updated in Ms-Excel


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1