Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Dec 2012
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lightbulb Please help me with this Macro

    Dear Paul,

    I have read quite a lot of threads here, and you are really a pool of information, thanks for your great posts that helped me a lot.

    My small business using word 2007 for invoicing, so far after study and copy your posts,

    i have made this code to let the system generate invoice numbers for all invoices.

    Code:
    Option Explicit
    
    Sub AutoNew()
    Dim InvoiceFile As String, InvNum As String
    'Save ini file in the Word startup folder.
    InvoiceFile = Options.DefaultFilePath(wdStartupPath) & "\Invoice.ini"
    'or, by using the following line, the Workgroup folder
    'InvoiceFile = Options.DefaultFilePath(wdWorkgroupTemplatesPath) & "\Invoice.ini"
    InvNum = System.PrivateProfileString(InvoiceFile, "InvoiceNumber", "InvNum")
    'If there is no InvoiceNumber reference in the ini file
    'Create one and set the number to 1, otherwise increment the number
    If InvNum = "" Then
      InvNum = 1
    Else
      InvNum = InvNum + 1
    End If
    System.PrivateProfileString(InvoiceFile, "InvoiceNumber", "InvNum") = InvNum
    With ActiveDocument
      .CustomDocumentProperties("InvNum") = InvNum
      .Fields.Update
    End With
    End Sub
    and this macro to let it save as PDF file with the invoice number as name into the same location as the word documents.

    Code:
    Sub SaveMe()
    Dim StrPath As String, InvNum2 As String
    
    With ActiveDocument
    InvNum2 = .CustomDocumentProperties("InvNum")
    End With
    
    
    ActiveDocument.SaveAs FileName:=ActiveDocument.Path & "\" & "Invoice #" & InvNum2, Fileformat:=wdFormatPDF
    
    
    End Sub
    and also made this to let the system to auto calculate the Due date which is 7 days from current date.

    Code:
    Option Explicit
    
    Sub DateAdd7()
    Dim myDate As Date
    Dim myRng As Range
      'Set the starting date with the value of a field
      Set myRng = ActiveDocument.Fields(3).Result
      myDate = myRng
      'Tip - Here the field index is one because it is the first field in the document.
      'If you don't know the field index number? Just select the field and run the following line of code:
      'MsgBox Selection.Fields(1).Index
      With ActiveDocument.Variables
        .Item("Date1").Value = Format(myDate + 7, "dd/MM/yyyy")
      End With
      ActiveDocument.Fields.Update
    lbl_Exit:
    Exit Sub
    End Sub
    Now I really would love to make this one step further, after done the research i couldn't find helpful information, so really need your help. Thanks in advance.

    I want to add another Macro that could email the invoice PDF file that has been created as an attachment, and also use the invoice file name as the subject which like "Inovice #1022"
    if you could merge this function into that save as PDF Macro that i have made that would be even better.


    p.s. my auto calculate 7 days Marco is not functioning, i don't know where i did wrong, and i also need to make another 3 macros which calculate 14days,30days and 45 days from now, do i only need to change the + 7 to 14,30 45?



    Don't know if you could get what i meant, let me know if you need anymore things from my side.



    <

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Take a look at post #3 here. The code is written to send Access reports saved to PDF as an attachment but you should be able to adapt it to Word easily. It is using Outlook as the Email client so if you are using another type of Email it might be a bit more challenging.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Dec 2012
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Dear RG

    Sorry about my poor knowledge in code language, i couldn't understand the code that you have posted in the other thread.

    I have tried to copy it to word into a new macro, but it seems not working?

    Could you please help me to put this function into the Word macro?


    Regards

  4. #4
    New Lounger
    Join Date
    Apr 2013
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    This might help

    '' All the variables starting with "m_" will need to be filled in, but I use this for a discvoery notification system n word 2007

    Code:
    Public Function sendEmail() As Boolean
    
     Dim bResult As Boolean
    
    On Error GoTo sendEmail_Error
    
    ''test vaidity of to address, just checking for the first
      Set oRegEx = CreateObject("vbscript.regexp")
      oRegEx.Pattern = "\b[A-Z0-9._%+-]+@(?:[A-Z0-9-]+\.)+[A-Z]{2,4}\b"
      bregresult = True ''oRegEx.test(m_semailSendToAddress)
      
      If bregresult = True And Len(m_semailSubject) > 0 Then
        Dim oEMail As Object
        Set oEMail = CreateObject("CDO.Message")
      
        ''Send out email
        oEMail.From = m_semailFromAddress
      
        oEMail.To = m_semailSendToAddress
      
        
        ''doing VERY basic validation here as email server will reject bad addresses, and these may be SSV values (;)
        If m_semailCCAddress <> "" And InStr(m_semailCCAddress, "@") > 0 Then _
          oEMail.CC = m_semailCCAddress
        If m_semailBCCAddress <> "" And InStr(m_semailBCCAddress, "@") > 0 Then _
          oEMail.BCC = m_semailBCCAddress
      
        
        oEMail.Subject = m_semailSubject
    
        ''check if html body, defautlt = false
        If m_bemailHtmlFormat Then
          oEMail.HTMLbody = m_semailBodyText
        Else
          oEMail.TextBody = m_semailBodyText
        End If
        
        ''chekc for attachments
        If Len(m_semailAttachmentPath) > 0 Then
          ''make sure we have a file
          If Len(Dir(m_semailAttachmentPath, vbNormal)) > 0 Then _
            oEMail.AddAttachment m_semailAttachmentPath
        End If
      
        ''configure for sending
        oEMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        oEMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = m_semailServerAddress
        oEMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        oEMail.Configuration.Fields.Update
        oEMail.Send
        ''clean up
        Set oEMail = Nothing
        bResult = True
      Else
        bResult = False
      End If
      
      sendEmail = bResult
    
    On Error GoTo 0
    Exit Function
    
    sendEmail_Error:
    
      MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure sendEmail of Class Module clsEmail"
      sendEmail = False
    End Function
    

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by oboise View Post
    This might help
    I can't see any relationship between your code and the topic of this thread...
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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