Results 1 to 7 of 7

Thread: Excel VBA: Auto-Emailer, Mark Important, Delivery Receipt etc.

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Feb 2014
    Posts
    74

    Question Excel VBA: Auto-Emailer, Mark Important, Delivery Receipt etc.

    Hi,

    I have attached some working code which I use for an auto emailer in one of my workbooks. I have tried unsuccessfully to amend this code so that it will also mark the email as "Important" as well as providing me a "Delivery Receipt". I know this is possible, I just cannot figure it out... and I know it's probably something extremely easy, but nonetheless it continually evades me. Please help. And thanks in advance.

    Code:
    Sub SendEmail()
    
        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 Columns("q").Cells.SpecialCells(xlCellTypeVisible)
            If cell.Value Like "*@*" Then
                EmailAddr = EmailAddr & ";" & cell.Value
            End If
        Next
        
        Msg = ""
        Msg = Msg & "All," & vbCrLf & vbCrLf
        Msg = Msg & "Please See Attachment. This is your copy of:  "
    
        Msg = Msg & Range("t6").Text & vbCrLf & vbCrLf 'Change the reference when needed -- DH
        Msg = Msg & "Of Contract #:  "
        Msg = Msg & Range("t5").Text & vbCrLf & vbCrLf 'Change the reference when needed -- DH
        Msg = Msg & "No Reply Necessary." & vbCrLf & vbCrLf
        Msg = Msg & "D.H." & vbCrLf
        Msg = Msg & "Contracts" & vbCrLf & vbCrLf & vbCrLf
            
        Subj = "New Contract For Your Copy"
        
        'Create Mail Item and view before sending
        Set MItem = OutlookApp.CreateItem(olMailItem)
        With MItem
            .To = EmailAddr
            .Subject = Subj
            .Body = Msg
            .Display
        End With
    
    End Sub

  2. #2

    Thread Starter
    Lively Member
    Join Date
    Feb 2014
    Posts
    74

    Re: Excel VBA: Auto-Emailer, Mark Important, Delivery Receipt etc.

    Also, if you have any alternate ways of writing this code which would simplify it, I'd welcome the learning experience. I know there has to be an easier way, this was the only way I could get it to work for me at the time. #StillLearning

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Excel VBA: Auto-Emailer, Mark Important, Delivery Receipt etc.

    try
    Code:
    .importance = olImportanceHigh
    .OriginatorDeliveryReportRequested = True
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Feb 2014
    Posts
    74

    Re: Excel VBA: Auto-Emailer, Mark Important, Delivery Receipt etc.

    thank you, it works perfectly. I had tried to make the .Importance = True ... would be nice if that would work as well, I wouldn't have known otherwise, but I know now.

    Do you know of any particular resources that teach these commands? Even just the most basic or to provide a clear understanding of the framework of VBA? I'm learning, but it feels like I'm learning it the hard way all the time.

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Excel VBA: Auto-Emailer, Mark Important, Delivery Receipt etc.

    but it feels like I'm learning it the hard way all the time.
    lol

    most of the information is in the vba help file, that is where i looked the value
    else google
    unfortunately, as there are multi values for importance, vba true = -1, is not one of them, false = 0 may well work, but not give the desired result to turn off
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Feb 2014
    Posts
    74

    Re: Excel VBA: Auto-Emailer, Mark Important, Delivery Receipt etc.

    Quote Originally Posted by westconn1 View Post
    lol

    most of the information is in the vba help file...
    Where is this VBA help file... why do I feel like Corky right now... This information might save me a lot of trouble.

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Excel VBA: Auto-Emailer, Mark Important, Delivery Receipt etc.

    Where is this VBA help file
    on the CD
    silly as it may seem the VBA help file is an optional install when installing office
    i always do a custom install so i can select VBA help and deselect the stupid office assistant (paperclip)

    F1 on any object property or method should bring up context help
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width