Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    I have set up a macro to email a message from Excel. I need the message to include the current month and years minus 30 days for eg if current month is August 2010, then the date in the body must read July 2010.

    .Body = "Attached please find summary profit figures for Branch1 as at" Month and Year to be included minus 30 days from current month

    Sub Mailfiles(sMailAddress As String, vFiles As Variant)
    Dim oMailItem As Object
    Dim oOLapp As Object
    Dim lCt As Long

    Set oOLapp = CreateObject("Outlook.application")
    Set oMailItem = oOLapp.CreateItem(0)
    With oMailItem
    .To = sMailAddress
    .Subject = "Summary Profit Figures"
    .Body = "Attached please find summary profit figures for Branch1"

    For lCt = LBound(vFiles) To UBound(vFiles)
    .attachments.Add CStr(vFiles(lCt))
    Next
    .Display
    Set oOLapp = Nothing
    Set oMailItem = Nothing
    End With

    Your assistance will be most appreciated


    End Sub

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    try this:

    sub test()

    zDate = CDate(Now)
    zDate = zDate - Day(zDate)
    zDate = Format(zDate, "mmmm yyyy")

    MsgBox (zDate)

    end sub


    Change
    Format(zDate, "mmmm yyyy")
    to
    Format(zDate, "mmm yyyy")
    ..if you don't want the full month name.

    You can use the above technique in your email sub

    zeddy


  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for the reply and your help. I have included your code in my Macro, but how can I get it to put the following message with the zDate into the body of my message. The date correctly shows July 2010, which I need to display in my message below.

    Attached please find summary profit figures for &zDate& Vs Prior Year . The message should appear as Attached please find summary profit figures for July 2010 Vs Prior Year. I am not sure how to link the zDate so that it inserts the date into the body of my message.

    Your assistance will be most appreciated

    Regards

    Howard

    PS Full code below

    Sub Mailfiles(sMailAddress As String, vFiles As Variant)
    zDate = CDate(Now)
    zDate = zDate - Day(zDate)
    zDate = Format(zDate, "mmmm yyyy")

    MsgBox (zDate)
    Dim oMailItem As Object
    Dim oOLapp As Object
    Dim lCt As Long

    Set oOLapp = CreateObject("Outlook.application")
    Set oMailItem = oOLapp.CreateItem(0)
    With oMailItem
    .To = sMailAddress
    .Subject = "Summary Profit Figures"
    .Body = "Attached please find summary profit figures for &zDate& Vs Prior Year"





    For lCt = LBound(vFiles) To UBound(vFiles)
    .attachments.Add CStr(vFiles(lCt))
    Next
    .Display
    Set oOLapp = Nothing
    Set oMailItem = Nothing

    End With


    End Sub

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Howard,

    Change: .Body = "Attached please find summary profit figures for &zDate& Vs Prior Year"
    To: .Body = "Attached please find summary profit figures for " & zDate & " Vs Prior Year"
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks for the help,much appreciated

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Retired Geek

    Thanks for all your help. I have one small question, how to I add some more line to the body of the macro

    i.e .Body = "Attached please find summary profit figures for &zDate& Vs Prior Year"

    second line to add "Regards"

    third line to add "Howard"

    Your assistance will be most appreciated

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Howard,

    I' m not sure about this, but I would try this:

    Body = "Attached please find summary profit figures for &zDate& Vs Prior Year" & _
    vbCrLf & vbCrLf & "Regards" & vbCrLf & "Howard"

    Note: the _ is a line continuation character if you code it all on one line delete this.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks for the help, much appreciated

Posting Permissions

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