+ Reply to Thread
Results 1 to 4 of 4

Copy data from excel to Outlook new email and create draft.

  1. #1
    Forum Contributor G.Bregvadze's Avatar
    Join Date
    12-06-2012
    Location
    Georgia
    MS-Off Ver
    Excel 2010 - 2016
    Posts
    256

    Copy data from excel to Outlook new email and create draft.

    HI,

    I am a moderate user of MS Excel.

    I have many tables in my Excel Sheet and have to send those tables daily to the users by Outlook mail.

    I am interested: is there any code or possibility that when I run a macro to copy the specific content from the sheet to outlook new mail, automatically indicate the specific title, in my case the current date, automatically indicate the specific user addresses. and save as draft.


    if needed I will upload the sample of data... thanks in advance.
    Last edited by G.Bregvadze; 05-07-2013 at 07:29 AM.

  2. #2
    Registered User
    Join Date
    02-10-2012
    Location
    Wales
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Copy data from excel to Outlook new email and create draft.

    Here we go.

    Sub SendEmail_to_BI() ' Go to tools and then references and ensure the 'Microsoft Outlook 12.0 Object Library' is ticked. Could be 14.0 etc if you have Office 2010


    Dim OutlookApp As Outlook.Application
    Dim MItem As Outlook.MailItem
    Dim cell As Range
    Dim Subj As String
    Dim EmailAddr As String
    Dim Recipient As String
    Dim msg As String

    'Create Outlook object
    Set OutlookApp = New Outlook.Application


    'Loop through the rows
    For Each cell In Range("I12:I13").Cells.SpecialCells(xlCellTypeVisible) ' These are the cells in the spreadsheet where the email addresses are stored.
    If cell.Value Like "*@*" Then
    EmailAddr = EmailAddr & ";" & cell.Value
    End If
    Next
    'add subject to message
    Subj = "This weeks volume file"

    'Compose message
    msg = "Hi Dave and Susana," & Recipient & vbCrLf & vbCrLf
    msg = msg & "" & vbCrLf & vbCrLf
    msg = msg & "Please find attached this weeks volume file." & vbCrLf & vbCrLf
    msg = msg & "" & vbCrLf & vbCrLf
    msg = msg & "Kind Regards," & vbCrLf & vbCrLf
    msg = msg & "The BAD Team" & vbCrLf



    'Create Mail Item and view before sending
    Set MItem = OutlookApp.CreateItem(olMailItem)
    With MItem
    .To = EmailAddr
    .Subject = Subj
    .Body = msg
    .Display 'change to .send if you want to send it before review

    'Attach File
    .attachments.Add ThisWorkbook.Path & "\" & "Latest ILP R4 VolumeFile.csv"
    End With

    End Sub

  3. #3
    Forum Contributor G.Bregvadze's Avatar
    Join Date
    12-06-2012
    Location
    Georgia
    MS-Off Ver
    Excel 2010 - 2016
    Posts
    256

    Re: Copy data from excel to Outlook new email and create draft.

    The code does not copy the specific data from sheet as it does not idnicated the specific cells.. also the title must be dynamic, in my case the current date must be displayed.... also it gives the error: user defined type not defined

  4. #4
    Forum Contributor G.Bregvadze's Avatar
    Join Date
    12-06-2012
    Location
    Georgia
    MS-Off Ver
    Excel 2010 - 2016
    Posts
    256

    Re: Copy data from excel to Outlook new email and create draft.

    PLease, help if anyone can guess what to do, if any additional information is needed, such as the sample file, or any detail I will provide.

+ 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