Jump to content
News Ticker
  • Welcome to the Community
  • Use the forum to troubleshoot your Excel & VBA Problems
  • We will be launching a new website soon! www.ExcelWTF.com/Learn ! Join us to learn VBA from the ground up!


  • Content Count

  • Joined

  • Last visited

  • Days Won


Sapron75 last won the day on August 9 2018

Sapron75 had the most liked content!

Community Reputation

4 Neutral

About Sapron75

  • Rank
    Newbie 2

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

  1. Hi, Recently I have Office 365 and also SharePoint with the new "Team Sites" environment (previously called workgroups). Anyhow, I have a macro which convert the excel sheet into pdf and puts this pdf in an email ready to send. This is the coding : sub sendPDF() Dim OutlookApp As Object Dim OutLookMailItem As Object Dim PdfFile As String, Title As String Dim myAttachments As Object ' Not sure for what the Title is Title = ActiveSheet.Range("D19") ' Define PDF filename PdfFile = ActiveWorkbook.FullName i = InStrRev(PdfFile, ".") If i > 1 Then PdfFile = Left(PdfFile, i - 1) ' PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf" dit is voor tabbladnaam erbij te geven PdfFile = PdfFile & "_concerning_" & ActiveSheet.Range("D19") & ".pdf" ' hiermee wordt de naam van persoon in bestand opgenomen ' Export activesheet as PDF With ActiveSheet .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False End With ' Use already open Outlook if possible On Error Resume Next Set OutlApp = GetObject(, "Outlook.Application") If Err Then Set OutlApp = CreateObject("Outlook.Application") IsCreated = True End If OutlApp.Visible = True On Error GoTo 0 ' Prepare e-mail with PDF attachment With OutlApp.CreateItem(0) ' Prepare e-mail .Subject = "MPR " & ActiveSheet.Range("H16") .To = "[email protected]" ' <-- Put email of the recipient here .Body = "Dear, " .Attachments.Add PdfFile .Display End With End Sub The excel file is located in a sharepoint team site, as it was previously in a workgroup. When running this macro, the e-mail is being prepared but when the pdf must be attached, Outlook will show the pdf as attachment, but underneath the pdfname there is notification of "Download failed" . Then I have to doubleclick it, then a notification comes up with the text, " you don't have the correct authorization" with two options "Again" and "Delete" . If I click again, then the pdf will be attached... :-( . How can I fix this ? Thank you ! Sapron75
  2. Hi Caleeco, thanks for the addition, it works great now. With this addition it will prevent to send the emails twice. If you have 2 persons, no problem, but a team can also be 20 persons, that is why I want to prevent sending double emails. But it works fine now. Finally it's done ! Thank you so much for you time spent in this case. You really helped me a lot !! I wish I could thank you personally, but that's not possible unfortunately. Thank you !! You rock dude!! Ps. I will come back to you later this month with the conditional formatting case... Sapron75
  3. Hi Caleeco, I found the problem. I needed to check the "Microsoft Word 14.0 Object Library" in "Tools-References". Now it works almost great :-) Almost for the part that I do can run the macro twice although there is a timestamp and "template used" available. So I checked the code and saw that this wasn't in : LastRow = .Range("C999").End(xlUp).Row 'Laatste rij in tabel bepalen For CustRow = 6 To LastRow TemplateBonusgrAfhank = .Range("AB" & CustRow).Value If TemplName <> .Range("Z" & CustRow).Value And TemplName = .Range("AB" & CustRow).Value Then I think this is the missing piece, but I can't integrate it in your code. Can you do this, then the code will be finished finally :-) Thanks again for all your effort and helping with this one ! Hope to hear from you very soon. Sapron
  4. Hi Caleeco, first thing I get is an error I didn't get this morning...strange... It sais that "Dim WordContent As Word.Range" has not been defined. Do you see straight away where the problem lies ? Thanks and then I can test it big time :-)
  5. Hi Caleeco, you rock man !!! I can't test the printing yet (next week), but the rest is exactly what I need. 1) Indeed the time stamp, very important! I assume this is the code you will use after the pdf or word is saved : .Range("Z" & CustRow).Value = TemplName .Range("AA" & CustRow).Value = Now 2) Printer I will test next week 3) I simply would say "Kill (wordFilename)" and "Kill (pdfFilename)" somewhere at the end :-) 4) Only code for closing Word, not Outlook, because I always have to review before I definitely send. So the email must be ready to send, and not immediately, so please set it on ".Display" . And Outlook is always running on my pc, so no need to close that. 5) I tested the outmail.send and works like a charm, but as said, must be set to .display 6) When you tidy up, can you adjust the variables for print/email and pdf/word. I didn't realize those 2 are just the headers for making a choice, so it's either print or email, and either word or pdf, so no doubles. Sorry for missing that earlier. This simplifies the code a bit. Again sorry !! Can you also put in this code when you are going to open Word ? On Error Resume Next Set WordApp = GetObject("Word.Application") If Err.Number <> 0 Then Err.Clear Just to avoid problems when Word already running. Probably not...but just to be sure :-) Thanks again !!! Sapron
  6. Hi Caleeco, The body of the email will be changed later, so for the time being please hold column D as an example name reference. I will expand the columns later with more data and then the body will be set up correctly. So at this point no changes have to be made in that regard. It has to be dependendent on the choises in g1 en g2. Mainly it will be email pdf but it can occur that I have to print them and send them by regular mail. And the Word/pdf choise also is a must. Thanks again Caleeco Sapron
  7. Hi Caleeco, That's exactly the goal ! And important is the columns "Templated used" and "Document sent" which have to be filled automatically after an email has been created, so I know what and when the email was created and no double email can be sent. But that's already in my code . Thanks a lot !!! Looking forward to it !!!! Sapron
  8. I think you are on the right track :-) Thanks so far and looking forward to see it running perfectly. If you have questions please let me know !
  9. Hi Caleeco, no the [email protected] emails are not being used. I should have deleted them, because the manager will get the emails. So for each teammember of the manager one email must be sent to that specific manager with multiple attachment (of only his teammembers). Sorry for the misunderstanding, so please don't look at the individual email addresses, but only the managers and their managers/HR managers (in cc.) So emails to column W addresses and cc to X and Y. Hope it is clear now :-) O, D1 is to select the template. We have several templates . To select the correct template, the letters will be made for only those who have that specific template.
  10. Hi Caleeco, No problem for the delay, I'm glad you are helping me. Sorry for the illegibility , I try to keep that in mind in future. The goal is that letters are being created for individual teammembers, but have to be sent to the manager's email (column "W") of that team, so this manager receives 1 email with all the letters for his team. Column "X" and "Y" have to be in the CC. Someone gave me a tip to do this , but in terms of coding I can't get it fixed . This is what the man said : You can do this with the following Steps with VBA 1. Create a Unique List of managers from the 'Adressen def' using Advanced Filters, and put this list in a separate column. (This Unique List can be from either the Manager Name or Manager Email) 2. Run From the First to the Last Name in this Unique List of Managers and for each Managers Name, use this Name as 'Criteria' in a New Advanced Filter. 3. The results will be all names that have this manager. 4. Then run a Loop for all Names within this list and create a unique PDF Filename based on the template assigned to this employee. For each template use .Add Attachment Filename to the single Email 5. Once the last Name in this list has been completed, Send The Email 6. Move the next Manager Name in the Unique list of managers and repeat steps 4 and 5. Thanks so much !! Sapron
  11. Hi Caleeco, I think this is a tough one to crack no ? I also tried it with an advanced filter before and after the email coding, but I just don't know how to manage it... Hopefully you will :-) Thanks again !
  12. Hi Caleeco, I just send you the email. Thanks in advance !!! Sapron
  13. Hi, I'm working on a vba project for creating pdf documents automatically with excel and then send it to 1 email recipient. If it is 1 document it's easy, but I want to send multiple pdf documents in the same group to the email address which is available in the excel sheet. I'm sure it is possible but I can't get it right. This is the code so far. With this coding individual emails are sent perfect, but not in group :-( Sub WordDocumentenmaken() Dim CusRow, CustCol, LastRow, TemplRow As Long Dim DocLoc, TagName, TagValue, TemplName, FileName, TemplateBonusgrAfhank As String Dim CurDt, LastAppDt As Date Dim WordDoc, WordApp, OutApp, OutMail As Object Dim WordContent As Word.Range With Blad16 If Range("B3").Value = Empty Then MsgBox "Please select a correct template from the drop down list" .Range("D1").Select Exit Sub End If TemplRow = .Range("B3").Value 'Template rij vaststellen TemplName = .Range("D1").Value 'Template naam vasstellen DocLoc = Blad1.Range("B" & TemplRow).Value 'Word document naam 'Open het word document template On Error Resume Next 'Als Word toevallig al loopt Set WordApp = GetObject("Word.Application") If Err.Number <> 0 Then 'Nieuw Word sessie starten Err.Clear 'Wanneer fout dan foutbehandeling Set WordApp = CreateObject("Word.Application") WordApp.Visible = True 'Maakt Word zichtbaar voor gebruiker End If LastRow = .Range("C999").End(xlUp).Row 'Laatste rij in tabel bepalen For CustRow = 6 To LastRow TemplateBonusgrAfhank = .Range("AB" & CustRow).Value If TemplName <> .Range("Z" & CustRow).Value And TemplName = .Range("AB" & CustRow).Value Then Set WordDoc = WordApp.Documents.Open(FileName:=DocLoc, ReadOnly:=False) 'Open de template For CustCol = 3 To 20 'Dit zijn alle kolommen met de tag naam TagName = .Cells(5, CustCol).Value 'Je geeft de rijnummer op waarin de tags staan vermeld TagValue = .Cells(CustRow, CustCol).Value ' Tag waarde With WordDoc.Content.Find .Text = TagName .Replacement.Text = TagValue .Wrap = wdFindContinue .Execute Replace:=wdReplaceAll 'Forward:=True, Wrap:=wdFindContinue End With Next CustCol If .Range("G1").Value = "PDF" Then FileName = ThisWorkbook.Path & "\" & .Range("C" & CustRow).Value & " " & .Range("F" & CustRow).Value & " " & .Range("G" & CustRow).Value & " " & .Range("H" & CustRow).Value & " - " & .Range("U" & CustRow).Value & ".pdf" 'creeert file met persnr,voorletter, tussenvoegsel,achternaam en Bonus subgroup WordDoc.ExportAsFixedFormat OutputFileName:=FileName, ExportFormat:=wdExportFormatPDF WordDoc.Close False Else 'Als het in Word moet FileName = ThisWorkbook.Path & "\" & .Range("C" & CustRow).Value & " " & .Range("F" & CustRow).Value & " " & .Range("G" & CustRow).Value & " " & .Range("H" & CustRow).Value & " - " & .Range("U" & CustRow).Value & ".docx" WordDoc.SaveAs FileName End If .Range("Z" & CustRow).Value = TemplName 'Template Name .Range("AA" & CustRow).Value = Now If .Range("G2").Value = "Email" Then Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.Createitem(0) With OutMail .To = Blad16.Range("W" & CustRow).Value .CC = Blad16.Range("X" & CustRow).Value & ";" & Blad16.Range("Y" & CustRow).Value .Subject = "Bonus letter(s) of your team" .Body = "Dear " & Blad16.Range("D" & CustRow).Value & " , attached you will find the bonus letter(s) for your team. Please ensure they receive this letter individually within 1 week after receiving this e-mail." .Attachments.Add FileName .Display 'Als je zonder van tevoren wilt zien dan .Display to.Send End With Else: WordDoc.PrintOut WordDoc.Close End If Kill (FileName) 'Gooit de pdf of word document weg wat je hebt aangemaakt End If Next CustRow WordApp.Quit End With End Sub The column "W" contain the managers of different people. If I run this code, individual emails are being created for the individual people. I need 1 email with the pdf's of every employee with the same templatename and same Manager email address. Can you help me with this one ? If you need the file, please let me know where to send it to. Thanks a lot !!
  14. Hi Caleeco, thank you for your answer. That wouldn't be an option, because the recipient can alter the data afterwards in excel and that's just the issue. For audit purposes the data must be data which can not be altered afterwards. So we will leave it like it is..no password thusfar, thank you for your input though !!
  15. Hi Caleeco, Thank you for your answer. Unfortunately I am , indeed, working on a restricted network which doesn't allow us to install third party software. I think I have to go to another solution, but not sure what I can do. The main goal is to send something to someone so that no one can read it unless they are supposed to. That's why I thought I could make a password protected pdf ... Do you have any other suggestions ? Thanks Ron
  • Create New...